<!DOCTYPE html><html lang="cn" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0"><title>SQL篇-MySQL数据库 | 碧蓝幻想</title><meta name="author" content="阿尔托莉雅"><meta name="copyright" content="阿尔托莉雅"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="1.数据库的基本概念：   名称 全称 简称    数据库 存储数据的仓库，数据是有组织的进行存储 DataBase( DB )   数据库管理系统 操作和管理数据库的大型软件 DataBase Management System( DBMS )   SQL 操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准 Structured Query Language( SQL )    什么">
<meta property="og:type" content="article">
<meta property="og:title" content="SQL篇-MySQL数据库">
<meta property="og:url" content="http://example.com/2023/03/13/4.%E6%95%B0%E6%8D%AE%E5%BA%93/A1.MySQL-%E6%AC%A7%E9%B9%8F%E7%AF%87/index.html">
<meta property="og:site_name" content="碧蓝幻想">
<meta property="og:description" content="1.数据库的基本概念：   名称 全称 简称    数据库 存储数据的仓库，数据是有组织的进行存储 DataBase( DB )   数据库管理系统 操作和管理数据库的大型软件 DataBase Management System( DBMS )   SQL 操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准 Structured Query Language( SQL )    什么">
<meta property="og:locale">
<meta property="og:image" content="http://example.com/images/Otherwallpaper/avatar.png">
<meta property="article:published_time" content="2023-03-13T12:32:38.437Z">
<meta property="article:modified_time" content="2023-03-13T12:48:15.089Z">
<meta property="article:author" content="阿尔托莉雅">
<meta property="article:tag" content="mySQL SQL 数据库">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://example.com/images/Otherwallpaper/avatar.png"><link rel="shortcut icon" href="/img/favicon.png"><link rel="canonical" href="http://example.com/2023/03/13/4.%E6%95%B0%E6%8D%AE%E5%BA%93/A1.MySQL-%E6%AC%A7%E9%B9%8F%E7%AF%87/index.html"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css" media="print" onload="this.media='all'"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.min.css" media="print" onload="this.media='all'"><script>const GLOBAL_CONFIG = { 
  root: '/',
  algolia: undefined,
  localSearch: undefined,
  translate: undefined,
  noticeOutdate: undefined,
  highlight: {"plugin":"highlighjs","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false},
  copy: {
    success: 'Copy successfully',
    error: 'Copy error',
    noSupport: 'The browser does not support'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  date_suffix: {
    just: 'Just',
    min: 'minutes ago',
    hour: 'hours ago',
    day: 'days ago',
    month: 'months ago'
  },
  copyright: undefined,
  lightbox: 'fancybox',
  Snackbar: undefined,
  source: {
    justifiedGallery: {
      js: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.js',
      css: 'https://cdn.jsdelivr.net/npm/flickr-justified-gallery/dist/fjGallery.min.css'
    }
  },
  isPhotoFigcaption: false,
  islazyload: false,
  isAnchor: false,
  percent: {
    toc: true,
    rightside: false,
  }
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'SQL篇-MySQL数据库',
  isPost: true,
  isHome: false,
  isHighlightShrink: false,
  isToc: true,
  postUpdate: '2023-03-13 20:48:15'
}</script><noscript><style type="text/css">
  #nav {
    opacity: 1
  }
  .justified-gallery img {
    opacity: 1
  }

  #recent-posts time,
  #post-meta time {
    display: inline !important
  }
</style></noscript><script>(win=>{
    win.saveToLocal = {
      set: function setWithExpiry(key, value, ttl) {
        if (ttl === 0) return
        const now = new Date()
        const expiryDay = ttl * 86400000
        const item = {
          value: value,
          expiry: now.getTime() + expiryDay,
        }
        localStorage.setItem(key, JSON.stringify(item))
      },

      get: function getWithExpiry(key) {
        const itemStr = localStorage.getItem(key)

        if (!itemStr) {
          return undefined
        }
        const item = JSON.parse(itemStr)
        const now = new Date()

        if (now.getTime() > item.expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return item.value
      }
    }
  
    win.getScript = url => new Promise((resolve, reject) => {
      const script = document.createElement('script')
      script.src = url
      script.async = true
      script.onerror = reject
      script.onload = script.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        script.onload = script.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(script)
    })
  
    win.getCSS = (url,id = false) => new Promise((resolve, reject) => {
      const link = document.createElement('link')
      link.rel = 'stylesheet'
      link.href = url
      if (id) link.id = id
      link.onerror = reject
      link.onload = link.onreadystatechange = function() {
        const loadState = this.readyState
        if (loadState && loadState !== 'loaded' && loadState !== 'complete') return
        link.onload = link.onreadystatechange = null
        resolve()
      }
      document.head.appendChild(link)
    })
  
      win.activateDarkMode = function () {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      win.activateLightMode = function () {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }
      const t = saveToLocal.get('theme')
    
          if (t === 'dark') activateDarkMode()
          else if (t === 'light') activateLightMode()
        
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        if (asideStatus === 'hide') {
          document.documentElement.classList.add('hide-aside')
        } else {
          document.documentElement.classList.remove('hide-aside')
        }
      }
    
    const detectApple = () => {
      if(/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)){
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
    })(window)</script><meta name="generator" content="Hexo 6.3.0"></head><body><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img is-center"><img src="/images/Otherwallpaper/avatar.png" onerror="onerror=null;src='/img/friend_404.gif'" alt="avatar"/></div><div class="sidebar-site-data site-data is-center"><a href="/archives/"><div class="headline">Articles</div><div class="length-num">11</div></a><a href="/tags/"><div class="headline">Tags</div><div class="length-num">4</div></a><a href="/categories/"><div class="headline">Categories</div><div class="length-num">2</div></a></div><hr/><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/archive/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 工具</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/images/"><i class="fa-fw fas fa-image"></i><span> 图库</span></a></li><li><a class="site-page child" href="/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/video/"><i class="fa-fw fas fa-video"></i><span> 视频</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 链接</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url('/images/PCwallpaper/backgroud2.jpg')"><nav id="nav"><span id="blog-info"><a href="/" title="碧蓝幻想"><span class="site-name">碧蓝幻想</span></a></span><div id="menus"><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/archive/"><i class="fa-fw fas fa-archive"></i><span> 归档</span></a></div><div class="menus_item"><a class="site-page group" href="javascript:void(0);"><i class="fa-fw fas fa-list"></i><span> 工具</span><i class="fas fa-chevron-down"></i></a><ul class="menus_item_child"><li><a class="site-page child" href="/images/"><i class="fa-fw fas fa-image"></i><span> 图库</span></a></li><li><a class="site-page child" href="/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/video/"><i class="fa-fw fas fa-video"></i><span> 视频</span></a></li></ul></div><div class="menus_item"><a class="site-page" href="/link/"><i class="fa-fw fas fa-link"></i><span> 链接</span></a></div><div class="menus_item"><a class="site-page" href="/about/"><i class="fa-fw fas fa-heart"></i><span> 关于</span></a></div></div><div id="toggle-menu"><a class="site-page" href="javascript:void(0);"><i class="fas fa-bars fa-fw"></i></a></div></div></nav><div id="post-info"><h1 class="post-title">SQL篇-MySQL数据库</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">Created</span><time class="post-meta-date-created" datetime="2023-03-13T12:32:38.437Z" title="Created 2023-03-13 20:32:38">2023-03-13</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">Updated</span><time class="post-meta-date-updated" datetime="2023-03-13T12:48:15.089Z" title="Updated 2023-03-13 20:48:15">2023-03-13</time></span><span class="post-meta-categories"><span class="post-meta-separator">|</span><i class="fas fa-inbox fa-fw post-meta-icon"></i><a class="post-meta-categories" href="/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库</a></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title="SQL篇-MySQL数据库"><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">Post View:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="post-content" id="article-container"><h2 id="1-数据库的基本概念："><a href="#1-数据库的基本概念：" class="headerlink" title="1.数据库的基本概念："></a>1.数据库的基本概念：</h2><table>
<thead>
<tr>
<th>名称</th>
<th>全称</th>
<th>简称</th>
</tr>
</thead>
<tbody><tr>
<td>数据库</td>
<td>存储数据的仓库，数据是有组织的进行存储</td>
<td>DataBase( DB )</td>
</tr>
<tr>
<td>数据库管理系统</td>
<td>操作和管理数据库的大型软件</td>
<td>DataBase Management System( DBMS )</td>
</tr>
<tr>
<td>SQL</td>
<td>操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准</td>
<td>Structured Query Language( SQL )</td>
</tr>
</tbody></table>
<ul>
<li><p>什么是数据库：</p>
<ul>
<li>数据库是“按照数据结构来组织、存储和管理数据的仓库</li>
<li>是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合</li>
</ul>
</li>
<li><p>数据库的特点：</p>
<ol>
<li>持久化存储数据。数据库也是一个文件系统</li>
<li>方便存储和管理数据</li>
<li>使用了统一的方式操作数据库—SQL</li>
</ol>
</li>
<li><p><strong>关系型数据库（RDBMS）</strong></p>
<ul>
<li>概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库</li>
<li>特点：<ul>
<li>使用表存储数据，格式统一，便于维护</li>
<li>使用SQL语言操作，标准统一，使用方便</li>
</ul>
</li>
</ul>
</li>
<li><p>非关系型数据库（Not Only SQL）：</p>
<ul>
<li>概念：是一种轻量、开源、不兼容 SQL 功能的数据库；</li>
<li>特点：<ul>
<li>强调 Key-Value 存储和文档数据库的优点</li>
</ul>
</li>
</ul>
</li>
</ul>
<h2 id="2-MySQL数据库的安装配置："><a href="#2-MySQL数据库的安装配置：" class="headerlink" title="2.MySQL数据库的安装配置："></a>2.MySQL数据库的安装配置：</h2><ul>
<li><p>仓库安装：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# rpm <span class="operator">-</span>ivh https:<span class="operator">/</span><span class="operator">/</span>repo.mysql.com<span class="operator">/</span><span class="operator">/</span>mysql80<span class="operator">-</span>community<span class="operator">-</span><span class="keyword">release</span><span class="operator">-</span>el9<span class="number">-1.</span>noarch.rpm</span><br><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# dnf install mysql<span class="operator">-</span>server <span class="operator">-</span>y</span><br><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# rpm <span class="operator">-</span>qa <span class="operator">|</span> grep mysql</span><br><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# systemctl restart mysqld</span><br><span class="line"># 分支一：</span><br><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# grep <span class="string">&#x27;temporary password&#x27;</span> <span class="operator">/</span>var<span class="operator">/</span>log<span class="operator">/</span>mysqld.log     # 输出的信息后面即为临时密码</span><br><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# mysql <span class="operator">-</span>uroot <span class="operator">-</span>p</span><br><span class="line"># 分支二：</span><br><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# vi <span class="operator">/</span>etc<span class="operator">/</span>my.cnf      # 在配置文件中添加<span class="keyword">skip</span><span class="operator">-</span><span class="keyword">grant</span><span class="operator">-</span>tables，奕斯伟跳过登录权限直接登录</span><br><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# systemctl restart mysqld</span><br><span class="line">[root<span class="variable">@localhost</span> <span class="operator">~</span>]# mysql               # 直接登录mysql数据库，无需密码</span><br><span class="line"># 先修改root先修改权限</span><br><span class="line"># 再修改root密码</span><br><span class="line"># 再修改root的host字段为<span class="operator">%</span>，意味为允许所有主机登录</span><br></pre></td></tr></table></figure>
</li>
<li><p>网络下载安装：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">[root@localhost mysql]# wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.32-1.el8.x86_64.rpm-bundle.tar</span><br><span class="line">[root@localhost mysql]# tar xvf mysql-8.0.32-1.el8.x86_64.rpm-bundle.tar</span><br><span class="line">[root@localhost mysql]# dnf localinstall mysql-community-server-8.0.32-1.el8.x86_64.rpm \</span><br><span class="line"><span class="meta prompt_">&gt; </span><span class="language-bash">mysql-community-client-8.0.32-1.el8.x86_64.rpm \</span></span><br><span class="line"><span class="language-bash">&gt; mysql-community-common-8.0.32-1.el8.x86_64.rpm \</span></span><br><span class="line"><span class="language-bash">&gt; mysql-community-icu-data-files-8.0.32-1.el8.x86_64.rpm \</span></span><br><span class="line"><span class="language-bash">&gt; mysql-community-client-plugins-8.0.32-1.el8.x86_64.rpm \</span></span><br><span class="line"><span class="language-bash">&gt; mysql-community-libs-8.0.32-1.el8.x86_64.rpm</span></span><br><span class="line">[root@localhost mysql]# systemctl start mysqld</span><br><span class="line">[root@localhost mysql]# grep &#x27;temporary password&#x27; /var/log/mysqld.log</span><br><span class="line">[root@localhost ~]# mysql -uroot -p</span><br><span class="line">Enter password:</span><br><span class="line"><span class="meta prompt_">mysql&gt; </span><span class="language-bash">ALTER USER <span class="string">&#x27;root&#x27;</span>@<span class="string">&#x27;localhost&#x27;</span> IDENTIFIED BY <span class="string">&#x27;上面文件中的临时密码&#x27;</span>;</span></span><br><span class="line"><span class="meta prompt_">mysql&gt; </span><span class="language-bash">show databases;</span></span><br><span class="line"><span class="meta prompt_">mysql&gt; </span><span class="language-bash"><span class="built_in">exit</span></span></span><br></pre></td></tr></table></figure>
</li>
<li><p>docker安装MySQL8.0.32：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">docker run -id \</span><br><span class="line">-p 3307:3306 \</span><br><span class="line">--name=b_mysql \</span><br><span class="line">-v $PWD/conf:/etc/mysql/mysql.conf.d \       </span><br><span class="line">-v $PWD/logs:/var/logs \</span><br><span class="line">-v $PWD/data:/var/lib/mysql \</span><br><span class="line">-e MYSQL_ROOT_PASSWORD=$Rui123456 \</span><br><span class="line">mysql:8.0.32</span><br></pre></td></tr></table></figure>
</li>
<li><p>（可选）MySQL8.0.32版本的数据库进行远程登录需要授权和更改加密规则：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">grant</span> <span class="keyword">all</span> <span class="keyword">on</span> <span class="operator">*</span>.<span class="operator">*</span> <span class="keyword">to</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;%&#x27;</span> <span class="keyword">with</span> <span class="keyword">grant</span> option;      <span class="comment">-- 给予root用户对所有数据库的所有权限</span></span><br><span class="line">flush privileges;      <span class="comment">-- 刷新权限</span></span><br><span class="line"><span class="keyword">update</span> mysql.user <span class="keyword">set</span> host<span class="operator">=</span><span class="string">&#x27;%&#x27;</span> <span class="keyword">where</span> <span class="keyword">user</span><span class="operator">=</span><span class="string">&#x27;qv123&#x27;</span>;      <span class="comment">-- 允许远程登录</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">user</span> <span class="string">&#x27;root&#x27;</span>@<span class="string">&#x27;%&#x27;</span> identified <span class="keyword">with</span> mysql_native_password <span class="keyword">by</span> <span class="string">&#x27;123456&#x27;</span>;      <span class="comment">-- 更新root用户密码</span></span><br><span class="line"><span class="comment">-- 查看用户信息表，plugin值为mysql_native_password的用户才可以被登录；即caching_sha2_password不行</span></span><br><span class="line"><span class="keyword">select</span> <span class="keyword">user</span>,host,plugin <span class="keyword">from</span> mysql.user;               </span><br><span class="line"># 重启数据库</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="3-MySQL数据库的服务操作："><a href="#3-MySQL数据库的服务操作：" class="headerlink" title="3.MySQL数据库的服务操作："></a>3.MySQL数据库的服务操作：</h2><ul>
<li><p>启动MySQL服务：（管理员）</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">net start mysql</span><br></pre></td></tr></table></figure>
</li>
<li><p>停止MySQL服务：（管理员）</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">net stop mysql</span><br></pre></td></tr></table></figure>
</li>
<li><p>登录MySQL数据库</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql -u用户名</span><br></pre></td></tr></table></figure>
</li>
<li><p>MySQL数据库的退出：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">exit</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看最大连接数</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show variables like &#x27;%max_connections%&#x27;;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询缓存配置情况</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show variables like &#x27;%query_cache%&#x27;;</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="4-数据库的数据类型"><a href="#4-数据库的数据类型" class="headerlink" title="4.数据库的数据类型:"></a>4.数据库的数据类型:</h2><table>
<thead>
<tr>
<th>数据类型</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>int</td>
<td>4字节整数类型</td>
</tr>
<tr>
<td>bigint</td>
<td>8字节长整数类型</td>
</tr>
<tr>
<td>float</td>
<td>4字节单精度浮点型</td>
</tr>
<tr>
<td>double</td>
<td>8字节双精度浮点型（默认）</td>
</tr>
<tr>
<td>date</td>
<td>日期，yyyy-MM-dd</td>
</tr>
<tr>
<td>datetime</td>
<td>日期，<strong>yyyy-MM-dd</strong> <strong>HH</strong>:mm <strong>ss</strong></td>
</tr>
<tr>
<td>timestamp</td>
<td>时间错类型 <strong>yyyy-MM-dd</strong> <strong>HH</strong>:mm ss</td>
</tr>
<tr>
<td>char</td>
<td>字符</td>
</tr>
<tr>
<td>varchar</td>
<td>字符串 <strong>varchar</strong>(20):姓名最大20个字符，此数据类型最大存储255字符</td>
</tr>
<tr>
<td>text</td>
<td>字符串，最多可容纳64KB</td>
</tr>
<tr>
<td>mediumtext</td>
<td>字符串，最多可容纳16MB</td>
</tr>
<tr>
<td>longtext</td>
<td>字符串，最多可容纳4GB</td>
</tr>
</tbody></table>
<h2 id="5-SQL语言："><a href="#5-SQL语言：" class="headerlink" title="5.SQL语言："></a>5.SQL语言：</h2><ul>
<li><p>概念：</p>
<ul>
<li>结构化查询语言(Structured Query Language)，在关系型数据库上执行数据操作、数据检索以及数据维护的标准语言。</li>
<li>SQL语言是非过程化的语言</li>
<li>使用SQL语句，程序员和数据库管理员可以完成如下的任务<ul>
<li>改变数据库的结构</li>
<li>更改系统的安全设置</li>
<li>增加用户对数据库或表的许可权限</li>
<li>在数据库中检索需要的信息</li>
<li>对数据库的信息进行更新</li>
</ul>
</li>
</ul>
</li>
<li><p>分类：</p>
<table>
<thead>
<tr>
<th>分类</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>(DDL)数据定义语言：</td>
<td>定义数据库对象：数据库，表，列等。关键字：创建表&#x2F;数据库，删除表&#x2F;数据库，修改表&#x2F;数据库等</td>
</tr>
<tr>
<td>(DML)数据库操作语言：</td>
<td>修改数据：<strong>insert</strong>,<strong>delete</strong>,updata等</td>
</tr>
<tr>
<td>(DQL)数据查询语言：</td>
<td>查询数据:<strong>select</strong>,<strong>where</strong>等</td>
</tr>
<tr>
<td>(DCL)数据控制语言：</td>
<td>定义数据的访问权限，及创建用户.关键字：<strong>GRANT</strong>,<strong>REVOKE</strong>等</td>
</tr>
<tr>
<td>(TCL)事务控制语言</td>
<td>代表关键字为commit、rollback</td>
</tr>
</tbody></table>
</li>
<li><p>书写规范：</p>
<ul>
<li>在数据库系统中，SQL语句不区分大小写(建议用大写) 。</li>
<li>但字符串常量区分大小写。</li>
<li>SQL语句可单行或多行书写，以“;”结尾。</li>
<li>关键词不能跨多行或简写。</li>
<li>用空格和缩进来提高语句的可读性。</li>
<li>子句通常位于独立行，便于编辑，提高可读性。</li>
<li>单行注释：“–”，多行注释：&#x2F;* text *&#x2F;</li>
</ul>
</li>
</ul>
<h2 id="6-操作数据库：CRUD"><a href="#6-操作数据库：CRUD" class="headerlink" title="6.操作数据库：CRUD:"></a>6.<em><strong>操作数据库：CRUD:</strong></em></h2><h3 id="1-C-Create-创建"><a href="#1-C-Create-创建" class="headerlink" title="1.C(Create):创建:"></a>1.C(<strong>Create</strong>):创建:</h3><ol>
<li><p>创建自己的数据库：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database 数据库名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建自己的数据库（先判断）：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database if <span class="keyword">not</span> <span class="keyword">exists</span> 数据库名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建自己的数据库,使用gbk的字符集:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database 数据名 <span class="type">character</span> <span class="keyword">set</span> gbk;</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建自己的数据库,判断是否存在，使用gbk的字符集:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database if <span class="keyword">not</span> <span class="keyword">exists</span> 数据库名 <span class="type">character</span> <span class="keyword">set</span> gbk;</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="2-R-show-查询"><a href="#2-R-show-查询" class="headerlink" title="2.R(show):查询:"></a>2.R(<strong>show</strong>):查询:</h3><ol>
<li><p>查询所有数据库的名称：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> databases;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询某个数据库的创建语句:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> database 数据库名称;</span><br></pre></td></tr></table></figure>
</li>
<li><p>模糊查询数据库：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">show databases like &#x27;__s%&#x27;;</span><br><span class="line"><span class="meta prompt_"># </span><span class="language-bash">%表示匹配任意个数的任意字符。</span></span><br><span class="line"><span class="meta prompt_"># </span><span class="language-bash">_表示单个任意字符。</span></span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="3-U-alter-修改"><a href="#3-U-alter-修改" class="headerlink" title="3.U(alter):修改:"></a>3.U(<strong>alter</strong>):修改:</h3><ol>
<li><p>修改数据库库的字符集:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> database 数据库名 <span class="type">character</span> <span class="keyword">set</span> 字符集名;</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="4-D（drop）-删除"><a href="#4-D（drop）-删除" class="headerlink" title="4.D（drop）:删除"></a>4.D（<strong>drop</strong>）:删除</h3><ol>
<li><p>删除数据库:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> database 数据库名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除数据库,如果存在就删除:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> database if <span class="keyword">exists</span> 数据库名;</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="5-使用数据库"><a href="#5-使用数据库" class="headerlink" title="5.使用数据库:"></a>5.使用数据库:</h3><ol>
<li><p>查询当期正在使用的数据库名:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> database();</span><br></pre></td></tr></table></figure>
</li>
<li><p>使用数据库:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">use 数据库名;</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="6-设置提示符显示当前数据库"><a href="#6-设置提示符显示当前数据库" class="headerlink" title="6.设置提示符显示当前数据库:"></a>6.设置提示符显示当前数据库:</h3><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql8-0-30 mysql]# vim /etc/my.cnf.d/mysql-server.cnf</span><br><span class="line">[mysql]</span><br><span class="line">prompt=mysql8.0 [\\d]&gt;</span><br><span class="line">[root@mysql8-0-30 mysql]# systemctl restart mysqld</span><br></pre></td></tr></table></figure>

<h3 id="7-数据库的字符集："><a href="#7-数据库的字符集：" class="headerlink" title="7.数据库的字符集："></a>7.数据库的字符集：</h3><ul>
<li><p>mysql字符集：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">- latin1支持西欧字符、希腊字符等</span><br><span class="line">- gbk支持中文简体字符</span><br><span class="line">- big5支持中文繁体字符</span><br><span class="line">- utf8几乎支持世界所有国家的字符。</span><br><span class="line">- utf8mb4是真正意义上的utf-8</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看所有的字符集：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show variables like &#x27;character%&#x27;;</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改mysql默认字符集</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta prompt_"># </span><span class="language-bash">1.在[mysqld]下添加</span></span><br><span class="line">character-set-server=utf8</span><br><span class="line">init_connect = &#x27;SET NAMES utf8&#x27;</span><br><span class="line"><span class="meta prompt_"># </span><span class="language-bash">2.在[client]下添加</span></span><br><span class="line">default-character-set=utf8</span><br></pre></td></tr></table></figure>
</li>
<li><p>MySQL5.8开始，官方建议使用utf8mb4。</p>
</li>
</ul>
<h3 id="8-MySQL系统数据库："><a href="#8-MySQL系统数据库：" class="headerlink" title="8.MySQL系统数据库："></a>8.MySQL系统数据库：</h3><table>
<thead>
<tr>
<th>数据库</th>
<th>含义</th>
</tr>
</thead>
<tbody><tr>
<td>mysql</td>
<td>存储MySQL服务器正常运行所需要的各种信息（时区，主从，用户，权限等）</td>
</tr>
<tr>
<td>information_schema</td>
<td>提供了访问数据库元数据的各种表和视图，包含数据库，表，字段类型及访问权限等</td>
</tr>
<tr>
<td>performance_schema</td>
<td>为MySQL服务器运行时状态提供了一个底层监控功能，主要用于收集数据库服务器性能参数</td>
</tr>
<tr>
<td>sys</td>
<td>包含了一系列方便DBA和开发人员利用performance_schema性能数据库进行性能调优的诊断的视图</td>
</tr>
</tbody></table>
<ul>
<li><p>由information_schema数据库负责维护：</p>
</li>
<li><p>分类：</p>
<table>
<thead>
<tr>
<th>字典</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>tables</td>
<td>存放数据库里所有的数据表、以及每个表所在数据库</td>
</tr>
<tr>
<td>schemata</td>
<td>存放数据库里所有的数据库信息</td>
</tr>
<tr>
<td>views</td>
<td>存放数据库里所有的视图信息。</td>
</tr>
<tr>
<td>columns</td>
<td>存放数据库里所有的列信息。</td>
</tr>
<tr>
<td>triggers</td>
<td>存放数据库里所有的触发器。</td>
</tr>
<tr>
<td>routines</td>
<td>存放数据库里所有存储过程和函数</td>
</tr>
<tr>
<td>key_column_usage</td>
<td>存放数据库所有的主外键</td>
</tr>
<tr>
<td>table_constraints</td>
<td>存放各个表的约束</td>
</tr>
<tr>
<td>statistics</td>
<td>存放了数据表的索引</td>
</tr>
</tbody></table>
</li>
</ul>
<h2 id="7-表的基本操作"><a href="#7-表的基本操作" class="headerlink" title="7.表的基本操作:"></a>7.表的基本操作:</h2><h3 id="1-C-Create-创建-1"><a href="#1-C-Create-创建-1" class="headerlink" title="1.C(Create):创建"></a>1.C(<strong>Create</strong>):创建</h3><ol>
<li><p>创建一个表:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(</span><br><span class="line">		列名<span class="number">1</span> 数据类型<span class="number">1</span>,</span><br><span class="line">		列名<span class="number">2</span> 数据类型<span class="number">2</span>,</span><br><span class="line">		列名<span class="number">3</span> 数据类型<span class="number">3</span>,</span><br><span class="line">		........</span><br><span class="line">		列名n 数据类型n</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
</li>
<li><p>创建表并带有注解</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(</span><br><span class="line">		列名<span class="number">1</span> 数据类型<span class="number">1</span> comment <span class="string">&#x27;...&#x27;</span>,</span><br><span class="line">		列名<span class="number">2</span> 数据类型<span class="number">2</span> comment <span class="string">&#x27;...&#x27;</span>,</span><br><span class="line">		列名<span class="number">3</span> 数据类型<span class="number">3</span> comment <span class="string">&#x27;...&#x27;</span>,</span><br><span class="line">		........</span><br><span class="line">		列名n 数据类型n comment <span class="string">&#x27;...&#x27;</span></span><br><span class="line">) comment <span class="string">&#x27;...&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>复制表结构:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名 <span class="keyword">like</span> 被复制的表名</span><br><span class="line"><span class="comment">-- 注意：最后一列不加逗号</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>复制表结构和表数据：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 新表名 <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 原表</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="2-R-show-查询-1"><a href="#2-R-show-查询-1" class="headerlink" title="2.R(show):查询"></a>2.R(<strong>show</strong>):查询</h3><ol>
<li><p>查询某个数据库中所有的表名称:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> tables;</span><br><span class="line"><span class="keyword">shoW</span> tables <span class="keyword">from</span> mysql;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询表结构：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">desc</span> 表名</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="3-U-alter-修改-1"><a href="#3-U-alter-修改-1" class="headerlink" title="3.U(alter):修改"></a>3.U(<strong>alter</strong>):修改</h3><ol>
<li><p>修改表名:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 rename <span class="keyword">to</span> 新的表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询表的字符集:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> database 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改表的字符集：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="type">character</span> <span class="keyword">set</span> utf8;</span><br></pre></td></tr></table></figure>
</li>
<li><p>添加一列：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> 列名 数据类型;</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改列名称，类型:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 change 列名 新列名 新数据类型;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 modify 列名 新数据类型;</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除列：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">drop</span> 列名;</span><br></pre></td></tr></table></figure></li>
</ol>
<h3 id="4-D（drop）-删除-1"><a href="#4-D（drop）-删除-1" class="headerlink" title="4.D（drop）:删除"></a>4.D（<strong>drop</strong>）:删除</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> 表名;</span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> if <span class="keyword">exists</span> 表名;</span><br></pre></td></tr></table></figure>

<h3 id="5-表的约束："><a href="#5-表的约束：" class="headerlink" title="5.表的约束："></a>5.表的约束：</h3><ul>
<li><p>约束是在表上强制执行的数据校验规则。约束主要<strong>用于保证数据库的完整性</strong>。当表中数据有相互依赖性时，可以保护相关的数据不被删除。</p>
</li>
<li><p>可以在创建表时规定约束（通过 CREATE TABLE 语句），或者在表创建之后通过 ALTER TABLE 语句规定约束。</p>
</li>
<li><p>根据约束数据列的限制，约束可分为：</p>
<ul>
<li>单列约束：每个约束只约束一列。</li>
<li>多列约束：每个约束可约束多列数据。</li>
</ul>
</li>
<li><p>根据约束的作用范围，约束可分为：</p>
<ul>
<li><strong>列级约束</strong>：只能作用在一个列上，跟在列的定义后面，语法：列定义 约束类型</li>
<li><strong>表级约束</strong>：可以作用在多个列上，不与列一起，而是单独定义</li>
</ul>
</li>
<li><p>列级约束类型：</p>
<table>
<thead>
<tr>
<th>约束关键字</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>primary key</td>
<td>主键(非空且唯一)约束，一张表只能有一个字段为主键（实体完整性）</td>
</tr>
<tr>
<td>foreign key</td>
<td>外键约束（参照完整性）</td>
</tr>
<tr>
<td>unique</td>
<td>唯一约束，规定某个字段在整个表中是唯一的（用户定义的完整性）</td>
</tr>
<tr>
<td>check</td>
<td>检查约束，检查列值是否满足一个条件表达式（用户定义的完整性）（8.0.1版本后出现）</td>
</tr>
<tr>
<td>not null</td>
<td>非空约束，规定某个字段不能为空（用户定义的完整性）</td>
</tr>
<tr>
<td>default</td>
<td>默认值约束</td>
</tr>
<tr>
<td>auto_increment</td>
<td>自动增长</td>
</tr>
</tbody></table>
</li>
<li><p>创建表时添加列级约束：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名( </span><br><span class="line">	id <span class="type">INT</span> <span class="keyword">primary</span> key auto_increment,</span><br><span class="line">	stuname <span class="type">varchar</span>(<span class="number">20</span>) <span class="keyword">not</span> <span class="keyword">null</span>,</span><br><span class="line">    age <span class="type">int</span> <span class="keyword">default</span> <span class="number">18</span>,</span><br><span class="line">    seat <span class="type">int</span> unque,</span><br><span class="line">    age <span class="type">int</span> <span class="keyword">check</span>(age <span class="operator">&gt;</span> <span class="number">18</span>),</span><br><span class="line">    dept_id <span class="type">int</span>,</span><br><span class="line">    <span class="keyword">foreign</span> key (dept_id) <span class="keyword">references</span> [外表名](外表列名)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改表的列级约束：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter table [表名] modify [字段名] [字段类型] [约束字段]</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除表的列级约束：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 删除NOT NULL约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> [表名] modify [列名] [字段类型];</span><br><span class="line"><span class="comment">-- 删除UNIQUE约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> [表名] <span class="keyword">drop</span> index [唯一约束名]</span><br><span class="line"><span class="comment">-- 删除PRIMARY KEY约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> [表名] <span class="keyword">drop</span> <span class="keyword">primary</span> key;</span><br><span class="line"><span class="comment">-- 删除FOREIGN KEY约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> [表名] <span class="keyword">drop</span> <span class="keyword">foreign</span> key [外键名]</span><br></pre></td></tr></table></figure>
</li>
<li><p>表级约束类型有四种：主键、外键、唯一、检查</p>
<table>
<thead>
<tr>
<th>约束关键字</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>primary key</td>
<td>主键(非空且唯一)约束</td>
</tr>
<tr>
<td>foreign key</td>
<td>外键约束</td>
</tr>
<tr>
<td>unique</td>
<td>唯一约束，规定某个字段在整个表中是唯一的</td>
</tr>
<tr>
<td>check</td>
<td>检查约束</td>
</tr>
</tbody></table>
</li>
<li><p>创建表时添加表级约束：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> tb_employee (</span><br><span class="line">	.......</span><br><span class="line">	<span class="keyword">constraint</span> <span class="string">&#x27;tb_employee_ibfk_1&#x27;</span> <span class="keyword">FOREIGN</span> KEY (dept_id) <span class="keyword">REFERENCES</span> <span class="string">&#x27;tb_dept&#x27;</span></span><br><span class="line">(`dept_id`)</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改表的表级约束：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> <span class="keyword">constraint</span> 约束名 约束类型(列名)</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除表级约束：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 删除NOT NULL约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> [表名] modify [列名] [字段类型];</span><br><span class="line"><span class="comment">-- 删除UNIQUE约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> [表名] <span class="keyword">drop</span> index [唯一约束名]</span><br><span class="line"><span class="comment">-- 删除PRIMARY KEY约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> [表名] <span class="keyword">drop</span> <span class="keyword">primary</span> key;</span><br><span class="line"><span class="comment">-- 删除FOREIGN KEY约束</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> [表名] <span class="keyword">drop</span> <span class="keyword">foreign</span> key [外键名]</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="8-MySQL的存储引擎："><a href="#8-MySQL的存储引擎：" class="headerlink" title="8.MySQL的存储引擎："></a>8.MySQL的存储引擎：</h2><h3 id="1-存储引擎的概念："><a href="#1-存储引擎的概念：" class="headerlink" title="1.存储引擎的概念："></a>1.存储引擎的概念：</h3><ul>
<li><p>存储引擎就是存储数据、建立索引、更新&#x2F;查询数据等技术的<strong>实现方式</strong>。<strong>存储引擎是基于表</strong>而不是基于库的，所以存储引擎也可以被称为<strong>表类型</strong>。</p>
</li>
<li><p><strong>默认存储引擎是InnoDB</strong>。</p>
</li>
<li><p>相关操作：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询建表语句</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">table</span> 表名;</span><br><span class="line"><span class="comment">-- 建表时指定存储引擎</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> 表名(</span><br><span class="line">    ...</span><br><span class="line">) engine<span class="operator">=</span>InnoDB;</span><br><span class="line"><span class="comment">-- 查看当前数据库支持的存储引擎</span></span><br><span class="line"><span class="keyword">show</span> engines;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="2-InnoDB，MyISAM，Memory的区别："><a href="#2-InnoDB，MyISAM，Memory的区别：" class="headerlink" title="2.InnoDB，MyISAM，Memory的区别："></a>2.InnoDB，MyISAM，Memory的区别：</h3><ul>
<li>InnoDB存储引擎：支持事务安全表（ACID），支持行锁定和外键</li>
<li>MyISAM存储引擎：拥有较高的插入、查询速度，但不支持事务</li>
<li>Memory存储引擎：将表中的数据存储到内存中，为查询和引用其他数据提供快速访问</li>
</ul>
<h3 id="4-存储引擎的操作："><a href="#4-存储引擎的操作：" class="headerlink" title="4.存储引擎的操作："></a>4.存储引擎的操作：</h3><ul>
<li><p>查询表的存储引擎：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show create table 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>建表时指定存储引擎</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">create table 表名(</span><br><span class="line">    ...</span><br><span class="line">) engine=InnoDB;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看当前数据库支持的存储引擎</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">show engines;</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="8-DCL-管理用户和授权-："><a href="#8-DCL-管理用户和授权-：" class="headerlink" title="8.DCL(管理用户和授权)："></a>8.DCL(管理用户和授权)：</h2><h3 id="1-管理用户："><a href="#1-管理用户：" class="headerlink" title="1.管理用户："></a>1.管理用户：</h3><ul>
<li><p>MySql8有新的安全要求，不能像之前的版本那样一次性创建用户并授权。需要先创建用户，再进行授权操作</p>
</li>
<li><p>添加用户：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;登录地址&#x27;</span> identified <span class="keyword">by</span> <span class="string">&#x27;密码&#x27;</span>;</span><br><span class="line"><span class="comment">-- 允许用户对所有数据库的权限，以及赋权权限</span></span><br><span class="line"><span class="keyword">grant</span> <span class="keyword">all</span> <span class="keyword">on</span> <span class="operator">*</span>.<span class="operator">*</span> <span class="keyword">to</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;%&#x27;</span> <span class="keyword">with</span> <span class="keyword">grant</span> option;</span><br><span class="line"><span class="comment">-- 撤销用户的所有权限(不包含赋权权限)</span></span><br><span class="line"><span class="keyword">revoke</span> <span class="keyword">all</span> <span class="keyword">on</span> <span class="operator">*</span>.<span class="operator">*</span> <span class="keyword">from</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br><span class="line"><span class="comment">-- 收回赋权权限</span></span><br><span class="line"><span class="keyword">revoke</span> <span class="keyword">grant</span> option <span class="keyword">on</span> <span class="operator">*</span>.<span class="operator">*</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br><span class="line"><span class="comment">-- 刷新权限</span></span><br><span class="line">flush privileges;</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除用户：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">user</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改用户</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> mysql.user <span class="keyword">set</span> host<span class="operator">=</span><span class="string">&#x27;%&#x27;</span> <span class="keyword">where</span> <span class="keyword">user</span><span class="operator">=</span><span class="string">&#x27;qv123&#x27;</span>;      <span class="comment">-- 允许远程登录</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>修改用户密码：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查看用户信息表，plugin值为mysql_native_password的用户才可以被登录；即caching_sha2_password不行</span></span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">user</span> <span class="string">&#x27;root&#x27;</span>@<span class="string">&#x27;%&#x27;</span> identified <span class="keyword">with</span> mysql_native_password <span class="keyword">by</span> <span class="string">&#x27;123456&#x27;</span>;      # 更新root用户密码</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询用户：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 1.切换到mysql数据库</span></span><br><span class="line"><span class="keyword">user</span> mysql;</span><br><span class="line"><span class="comment">-- 2.查询user表</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="2-授权："><a href="#2-授权：" class="headerlink" title="2.授权："></a>2.授权：</h3><ul>
<li><p>权限分类：</p>
<table>
<thead>
<tr>
<th>权限</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>usage</td>
<td>只能登录</td>
</tr>
<tr>
<td>select</td>
<td>插入数据</td>
</tr>
<tr>
<td>update</td>
<td>修改数据</td>
</tr>
<tr>
<td>delete</td>
<td>删除数据</td>
</tr>
<tr>
<td>select</td>
<td>只能查询指定表</td>
</tr>
<tr>
<td>alter</td>
<td>修改表</td>
</tr>
<tr>
<td>drop</td>
<td>删除数据库 &#x2F; 表  &#x2F;  视图</td>
</tr>
<tr>
<td>create</td>
<td>创建数据库 &#x2F; 表</td>
</tr>
<tr>
<td>all</td>
<td>能对指定数据库进行任意操作</td>
</tr>
</tbody></table>
</li>
<li><p>查询权限:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> grants <span class="keyword">for</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>授予权限：grant</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">grant</span> [权限<span class="number">1</span>,....] <span class="keyword">on</span> [数据库名.[表名]] <span class="keyword">to</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span> [identified <span class="keyword">by</span> <span class="string">&#x27;密码&#x27;</span>] [<span class="keyword">with</span> option 参数];</span><br><span class="line"># 例：</span><br><span class="line"><span class="comment">-- grant usage on *.* to&#x27;qv123&#x27;@&#x27;localhost&#x27;;  -- 只能登录</span></span><br><span class="line"><span class="comment">-- grant select on springboot.book to &#x27;qv123&#x27;@&#x27;localhost&#x27;;  -- 只能查询指定表</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>with option参数：</p>
<table>
<thead>
<tr>
<th>选项</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>MAX_QUERIES_PER_HOUR</td>
<td>定义每小时允许执行的查询数</td>
</tr>
<tr>
<td>MAX_UPDATES_PER_HOUR</td>
<td>定义每小时允许执行的更新数</td>
</tr>
<tr>
<td>MAX_CONNECTIONS_PER_HOUR</td>
<td>定义每小时可以建立的连接数</td>
</tr>
<tr>
<td>MAX_USER_CONNECTIONS</td>
<td>定义单个用户同时可以建立的连接数</td>
</tr>
</tbody></table>
</li>
<li><p>撤销权限：revoke</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">revoke</span> [权限<span class="number">1</span>,....] <span class="keyword">on</span> [数据库名.[表名]] <span class="keyword">from</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br><span class="line"># 例：</span><br><span class="line"><span class="comment">-- revoke all on *.* from dba@localhost;    -- 移除所有权限</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>刷新权限：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">flush privileges;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="3-密码策略："><a href="#3-密码策略：" class="headerlink" title="3.密码策略："></a>3.密码策略：</h3><ul>
<li><p>查看临时密码：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">awk &#x27;/temporary password/ &#123;print $NF&#125;&#x27; /var/log/mysqld.log</span><br><span class="line"><span class="meta prompt_"># </span><span class="language-bash">or</span></span><br><span class="line">grep &#x27;password&#x27; /var/log/mysqld.log</span><br></pre></td></tr></table></figure>
</li>
<li><p>密码策略：</p>
<table>
<thead>
<tr>
<th>Policy</th>
<th>Tests Performed</th>
</tr>
</thead>
<tbody><tr>
<td>0 or LOW</td>
<td>Length</td>
</tr>
<tr>
<td>1 or MEDIUM</td>
<td>Length; numeric, lowercase&#x2F;uppercase, and special characters</td>
</tr>
<tr>
<td>2 or STRONG</td>
<td>Length; numeric, lowercase&#x2F;uppercase, and special characters; dictionary file</td>
</tr>
</tbody></table>
</li>
<li><p>查看密码插件：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> variables <span class="keyword">like</span> <span class="string">&#x27;validate_password%&#x27;</span>;</span><br><span class="line"># 内容如下</span><br><span class="line"><span class="operator">+</span><span class="comment">--------------------------------------+--------+</span></span><br><span class="line"><span class="operator">|</span> Variable_name                        <span class="operator">|</span> <span class="keyword">Value</span>  <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------------------------------------+--------+</span></span><br><span class="line"><span class="operator">|</span> validate_password.check_user_name    <span class="operator">|</span> <span class="keyword">ON</span>     <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.dictionary_file    <span class="operator">|</span>        <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.length             <span class="operator">|</span> <span class="number">8</span>      <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.mixed_case_count   <span class="operator">|</span> <span class="number">1</span>      <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.number_count       <span class="operator">|</span> <span class="number">1</span>      <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.policy             <span class="operator">|</span> MEDIUM <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.special_char_count <span class="operator">|</span> <span class="number">1</span>      <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------------------------------------+--------+</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>如果没有插件可以通过安装的形式：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">install component <span class="string">&#x27;file://component_validate_password&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>更改密码策略：</p>
<ul>
<li><p>方法一：临时修改：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 更改密码策略为LOW，改为LOW或0</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> validate_password.policy<span class="operator">=</span>LOW;</span><br><span class="line"><span class="comment">-- 更改密码长度</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> validate_password.length<span class="operator">=</span><span class="number">0</span>;</span><br><span class="line"><span class="comment">-- 设置大小写、数字和特殊字符均不要求。</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> validate_password.special_char_count<span class="operator">=</span><span class="number">0</span>;     <span class="comment">-- 特殊字符</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> validate_password.mixed_case_count<span class="operator">=</span><span class="number">0</span>;       <span class="comment">-- 大小写</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> validate_password.number_count<span class="operator">=</span><span class="number">0</span>;           <span class="comment">-- 数字</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>方法二：初始化时不启用，只需要在初始化时指定 –initialize-insecure 即可</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqld <span class="comment">--initialize-insecure --datadir=/var/lib/mysql --basedir=/usr --user=mysql</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>方法三：修改配置文件</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">vim /etc/my.cnf</span><br><span class="line"><span class="meta prompt_"># </span><span class="language-bash">增加内容</span></span><br><span class="line">[mysqld]</span><br><span class="line">validate_password=off</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改完毕，重启服务</p>
</li>
</ul>
</li>
<li><p>查看数据库当前密码策略</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> variables <span class="keyword">like</span> &quot;%password%&quot;;</span><br><span class="line"># 内容如下：</span><br><span class="line"><span class="operator">+</span><span class="comment">----------------------------------------------+-----------------+</span></span><br><span class="line"><span class="operator">|</span> Variable_name                                <span class="operator">|</span> <span class="keyword">Value</span>           <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">----------------------------------------------+-----------------+</span></span><br><span class="line"><span class="operator">|</span> caching_sha2_password_auto_generate_rsa_keys <span class="operator">|</span> <span class="keyword">ON</span>              <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> caching_sha2_password_digest_rounds          <span class="operator">|</span> <span class="number">5000</span>            <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> caching_sha2_password_private_key_path       <span class="operator">|</span> private_key.pem <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> caching_sha2_password_public_key_path        <span class="operator">|</span> public_key.pem  <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> default_password_lifetime                    <span class="operator">|</span> <span class="number">0</span>               <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> disconnect_on_expired_password               <span class="operator">|</span> <span class="keyword">ON</span>              <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> generated_random_password_length             <span class="operator">|</span> <span class="number">20</span>              <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> mysql_native_password_proxy_users            <span class="operator">|</span> OFF             <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> password_history                             <span class="operator">|</span> <span class="number">0</span>               <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> password_require_current                     <span class="operator">|</span> OFF             <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> password_reuse_interval                      <span class="operator">|</span> <span class="number">0</span>               <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> report_password                              <span class="operator">|</span>                 <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> sha256_password_auto_generate_rsa_keys       <span class="operator">|</span> <span class="keyword">ON</span>              <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> sha256_password_private_key_path             <span class="operator">|</span> private_key.pem <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> sha256_password_proxy_users                  <span class="operator">|</span> OFF             <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> sha256_password_public_key_path              <span class="operator">|</span> public_key.pem  <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.check_user_name            <span class="operator">|</span> <span class="keyword">ON</span>              <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.dictionary_file            <span class="operator">|</span>                 <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.length                     <span class="operator">|</span> <span class="number">8</span>               <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.mixed_case_count           <span class="operator">|</span> <span class="number">1</span>               <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.number_count               <span class="operator">|</span> <span class="number">1</span>               <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.policy                     <span class="operator">|</span> LOW             <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> validate_password.special_char_count         <span class="operator">|</span> <span class="number">1</span>               <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">----------------------------------------------+-----------------+</span></span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="9-MySQL的DML语句"><a href="#9-MySQL的DML语句" class="headerlink" title="9.MySQL的DML语句"></a>9.MySQL的DML语句</h2><h3 id="1-insert-x2F-replace语句：添加数据"><a href="#1-insert-x2F-replace语句：添加数据" class="headerlink" title="1. insert&#x2F; replace语句：添加数据"></a>1. insert&#x2F; replace语句：添加数据</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[<span class="keyword">insert</span><span class="operator">/</span>replace] <span class="keyword">into</span> 表名 (列名<span class="number">1</span>,列名<span class="number">2</span>,...列名n) <span class="keyword">values</span> (值<span class="number">1</span>,值<span class="number">2</span>,...值n);		</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[<span class="keyword">insert</span><span class="operator">/</span>replace] <span class="keyword">into</span> 表名 <span class="keyword">set</span> 字段<span class="number">1</span><span class="operator">=</span>值<span class="number">1</span>,字段<span class="number">2</span><span class="operator">=</span>值<span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 复制数据，已经存在一张结构一致的表</span><br><span class="line">[<span class="keyword">insert</span><span class="operator">/</span>replace] <span class="keyword">into</span> 新表 <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 原表</span><br></pre></td></tr></table></figure>

<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta prompt_"># </span><span class="language-bash">REPLACE与INSERT语句区别：</span></span><br><span class="line"><span class="meta prompt_"># </span><span class="language-bash">使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值与已有记录相同，则已有记录先被删除，然后再插入新记录。</span></span><br><span class="line"><span class="meta prompt_"># </span><span class="language-bash">使用replace的最大好处就是可以将delete和insert合二为一</span></span><br></pre></td></tr></table></figure>

<h3 id="2-delete-x2F-truncate-x2F-drop语句：删除数据："><a href="#2-delete-x2F-truncate-x2F-drop语句：删除数据：" class="headerlink" title="2.delete &#x2F; truncate&#x2F;drop语句：删除数据："></a>2.delete &#x2F; truncate&#x2F;drop语句：删除数据：</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件;       <span class="comment">-- 删除数据，保留表结构，可以回滚，如果数据量大，很慢</span></span><br><span class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> 表名 <span class="keyword">where</span> 条件;    <span class="comment">-- 删除所有数据，保留表结构，不可以回滚，速度相对很快,然后在创建一个一模一样的空表</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> 表名 <span class="keyword">where</span> 条件;        <span class="comment">-- 删除数据和表结构,删除速度最快。</span></span><br></pre></td></tr></table></figure>

<h3 id="3-update语句：修改数据："><a href="#3-update语句：修改数据：" class="headerlink" title="3.update语句：修改数据："></a>3.update语句：修改数据：</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> 表名 <span class="keyword">set</span> 列名<span class="number">1</span><span class="operator">=</span>值<span class="number">1</span>，列名<span class="number">2</span><span class="operator">=</span>值<span class="number">2</span>,...<span class="keyword">where</span> 条件;</span><br><span class="line"><span class="comment">-- 注意:</span></span><br><span class="line"><span class="comment">--     1.如果不加任何条件，则会将表中所有记录全部删除</span></span><br></pre></td></tr></table></figure>

<h3 id="4-select语句：查询数据"><a href="#4-select语句：查询数据" class="headerlink" title="4.select语句：查询数据"></a>4.select语句：查询数据</h3><h4 id="1-基础查询"><a href="#1-基础查询" class="headerlink" title="1.基础查询"></a>1.基础查询</h4><ul>
<li><p>查询表中某些记录</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 列名，列名 <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询表中所有记录</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询表中不重复的记录</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> 列名 <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询并计算表中某些记录之和</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 列名,列名,列名<span class="operator">+</span>列名 <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询并计算表中某些记录之和（如果值为NULL，值替换为0）</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 列名,列名,列名<span class="operator">+</span>ifnull(列名,<span class="number">0</span>) <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查询并计算表中某些记录之和（如果值为NULL），改为0,起别名</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 列名 <span class="keyword">as</span> 列名,列名 <span class="keyword">as</span> 列名,列名<span class="operator">+</span>ifnull(列名,<span class="number">0</span>) <span class="keyword">as</span> 列名 <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure></li>
</ul>
<h4 id="2-条件查询"><a href="#2-条件查询" class="headerlink" title="2.条件查询"></a>2.条件查询</h4><ul>
<li><p>where子句后跟条件</p>
<table>
<thead>
<tr>
<th>运算符</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>&lt; &lt;&#x3D; &gt;&#x3D; &#x3D; &lt;&gt;</td>
<td>大于小于等于</td>
</tr>
<tr>
<td>between…and</td>
<td>之间</td>
</tr>
<tr>
<td>like</td>
<td>模糊查询</td>
</tr>
<tr>
<td>_ 单个任意字符 %任意多个字符</td>
<td>占位符</td>
</tr>
<tr>
<td>is null is not null</td>
<td>判断是否为null</td>
</tr>
<tr>
<td>and  或者  &amp;&amp;</td>
<td>和</td>
</tr>
<tr>
<td>or 或者</td>
<td>或</td>
</tr>
<tr>
<td>not 或者 !</td>
<td>非</td>
</tr>
<tr>
<td>regexp</td>
<td>正则表达式</td>
</tr>
</tbody></table>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> last_name, manager_id <span class="keyword">FROM</span> employees <span class="keyword">WHERE</span> manager_id <span class="keyword">IS</span> <span class="keyword">NULL</span>;</span><br></pre></td></tr></table></figure>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> NAME <span class="keyword">LIKE</span> &quot;王%&quot;;<span class="comment">---第一个字为“王”的记录</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> NAME <span class="keyword">LIKE</span> &quot;_麻%&quot;;<span class="comment">---第二个字为“麻”的记录</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> NAME <span class="keyword">LIKE</span> &quot;__&quot;;<span class="comment">---名为两个字的记录</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> NAME <span class="keyword">LIKE</span> &quot;%马%&quot;;<span class="comment">---名中含“马”的记录</span></span><br></pre></td></tr></table></figure>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> NAME <span class="keyword">FROM</span> worker <span class="keyword">WHERE</span> <span class="built_in">FLOOR</span>(salary) <span class="keyword">BETWEEN</span> <span class="number">1000</span> <span class="keyword">AND</span> <span class="number">2000</span>;</span><br></pre></td></tr></table></figure></li>
</ul>
<h4 id="3-排序查询"><a href="#3-排序查询" class="headerlink" title="3.排序查询"></a>3.排序查询</h4><table>
<thead>
<tr>
<th>排序方式</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>asc</td>
<td>升序（默认）</td>
</tr>
<tr>
<td>desc</td>
<td>降序</td>
</tr>
</tbody></table>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表名 <span class="keyword">order</span> <span class="keyword">by</span> 列名;<span class="comment">---默认排序（从小到大）</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表名 <span class="keyword">order</span> <span class="keyword">by</span> 列名 排序方式;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表名 <span class="keyword">order</span> <span class="keyword">by</span> 列名<span class="number">1</span> 排序方式<span class="number">1</span>,列名<span class="number">2</span> 排序方式<span class="number">2</span>;<span class="comment">---如果列名1值相同，列名1相同记录按列名2，排序方式2排序</span></span><br></pre></td></tr></table></figure>



<h4 id="4-分组查询"><a href="#4-分组查询" class="headerlink" title="4.分组查询"></a>4.分组查询</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 列名<span class="number">1</span>,<span class="built_in">avg</span>(math) <span class="keyword">from</span> 表名 <span class="keyword">group</span> <span class="keyword">by</span> 列名<span class="number">1</span>;</span><br><span class="line"><span class="keyword">select</span> 列名<span class="number">1</span>,<span class="built_in">avg</span>(math) <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件 <span class="keyword">group</span> <span class="keyword">by</span> 列名<span class="number">1</span>;</span><br><span class="line"><span class="keyword">select</span> 列名<span class="number">1</span>,<span class="built_in">avg</span>(math) <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件 <span class="keyword">group</span> <span class="keyword">by</span> 列名<span class="number">1</span> <span class="keyword">having</span> <span class="built_in">count</span>(id)<span class="operator">&gt;</span><span class="number">2</span>;</span><br><span class="line"><span class="comment">-- 注意:</span></span><br><span class="line"><span class="comment">--     where 与having：where在分组之前进行限定，having在分组之后进行限定</span></span><br><span class="line"><span class="comment">--     where后不能跟聚合函数，having可以跟聚合函数的判断</span></span><br></pre></td></tr></table></figure>



<h4 id="5-分页查询"><a href="#5-分页查询" class="headerlink" title="5.分页查询"></a>5.分页查询</h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 公式:开始的索引=(当前的页码-1)*每页显示的条数</span></span><br><span class="line"><span class="comment">-- 公式:当前的页码=开始的索引/每页显示的条数 + 1</span></span><br><span class="line"><span class="comment">-- select * from 表名 limit 开始的索引,条数; --第一页，三条记录</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> book LIMIT <span class="number">0</span>,<span class="number">3</span>;</span><br><span class="line"><span class="comment">-- select * from 表名 limit 开始的索引,条数; --第二页，三条记录</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> book LIMIT <span class="number">3</span>,<span class="number">3</span>;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> book  <span class="keyword">WHERE</span> NAME <span class="keyword">LIKE</span> &quot;%张%&quot; LIMIT <span class="number">0</span>,<span class="number">3</span>;</span><br></pre></td></tr></table></figure>



<h4 id="6-多表查询"><a href="#6-多表查询" class="headerlink" title="6.多表查询:"></a>6.多表查询:</h4><ul>
<li><p>笛卡尔积:</p>
<p> 有两个集合A,B 取这两个集合的所有组成情况；要完成多表查询,需要消除无用的数据</p>
</li>
<li><p><strong>内连接查询</strong></p>
<ol>
<li><p>隐式内连接：(自连接查询)</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 表<span class="number">1.</span>列名<span class="number">2</span>,表<span class="number">2.</span>列名<span class="number">2</span> <span class="keyword">from</span> 表<span class="number">1</span>,表<span class="number">2</span> <span class="keyword">where</span> 表<span class="number">1.</span><span class="string">&#x27;列名1&#x27;</span><span class="operator">=</span>表<span class="number">2.</span><span class="string">&#x27;列名1&#x27;</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>显式内连接：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 表<span class="number">1.</span>列名<span class="number">2</span>,表<span class="number">2.</span>列名<span class="number">2</span> <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 表<span class="number">1.</span><span class="string">&#x27;列名1&#x27;</span><span class="operator">=</span>表<span class="number">2.</span><span class="string">&#x27;列名1&#x27;</span></span><br></pre></td></tr></table></figure></li>
</ol>
</li>
<li><p><strong>外连接查询</strong></p>
<ol>
<li><p>左外连接</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询的是左表所有数据以及交集部分</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">left</span> <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件;</span><br><span class="line"></span><br><span class="line"><span class="comment">-- 查询所有学生的选课情况（包括没有选课的学生）</span></span><br><span class="line"><span class="keyword">SELECT</span> sname,sc.cno <span class="keyword">FROM</span> student <span class="keyword">LEFT</span> <span class="keyword">JOIN</span> sc <span class="keyword">ON</span> sc.sno <span class="operator">=</span> student.sno;</span><br></pre></td></tr></table></figure>
</li>
<li><p>右外连接</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查询的是右表所有数据以及交集部分</span></span><br><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">right</span> <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件;</span><br></pre></td></tr></table></figure></li>
</ol>
</li>
</ul>
<h4 id="7-嵌套查询："><a href="#7-嵌套查询：" class="headerlink" title="7.嵌套查询："></a>7.嵌套查询：</h4><table>
<thead>
<tr>
<th align="left">操作符</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">IN</td>
<td align="left">在指定的集合范围内，多选一</td>
</tr>
<tr>
<td align="left">NOT IN</td>
<td align="left">不在指定的集合范围内</td>
</tr>
<tr>
<td align="left">ANY</td>
<td align="left">子查询返回列表中，有任意一个满足即可</td>
</tr>
<tr>
<td align="left">SOME</td>
<td align="left">与ANY等同，使用SOME的地方都可以使用ANY</td>
</tr>
<tr>
<td align="left">ALL</td>
<td align="left">子查询返回列表的所有值都必须满足</td>
</tr>
</tbody></table>
<ul>
<li><p>概念：</p>
<p> 查询中嵌套查询，称嵌套查询为子查询</p>
</li>
<li><p>带IN的子查询：(列子查询)</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- IN 和 NOT IN</span></span><br><span class="line"><span class="keyword">select</span> sno,sname <span class="keyword">from</span> student <span class="keyword">where</span> sno <span class="keyword">in</span> (<span class="keyword">select</span> sno <span class="keyword">from</span> sc <span class="keyword">where</span> cno<span class="operator">=</span><span class="number">1</span>);</span><br></pre></td></tr></table></figure>
</li>
<li><p>带有比较运算符的子查询：（标量子查询）</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> sno,cno <span class="keyword">from</span> sc x <span class="keyword">where</span> grade <span class="operator">&gt;=</span>(<span class="keyword">select</span> <span class="built_in">avg</span>(grade) <span class="keyword">from</span> sc y <span class="keyword">where</span> y.sno<span class="operator">=</span>x.sno);</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id<span class="operator">=</span>(<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;销售部&#x27;</span>);</span><br></pre></td></tr></table></figure>
</li>
<li><p>带有ANY或All的子查询：(列子查询)</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- &gt;ANY 大于子查询结果中的某个值</span></span><br><span class="line"><span class="comment">-- &gt;ALL 大于子查询结果中的所有值</span></span><br><span class="line"><span class="keyword">select</span> sname,sage <span class="keyword">from</span> student <span class="keyword">where</span> sage<span class="operator">&lt;</span><span class="keyword">any</span>(<span class="keyword">select</span> sage <span class="keyword">from</span> student <span class="keyword">where</span> sdept<span class="operator">=</span><span class="string">&#x27;CS&#x27;</span>);</span><br><span class="line"><span class="keyword">select</span> sname,sage <span class="keyword">from</span> student <span class="keyword">where</span> sage<span class="operator">&lt;</span><span class="keyword">all</span>(<span class="keyword">select</span> sage <span class="keyword">from</span> student <span class="keyword">where</span> sdept<span class="operator">=</span><span class="string">&#x27;CS&#x27;</span>);</span><br></pre></td></tr></table></figure>
</li>
<li><p>（行子查询）</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 通过查询张无忌的薪资和直属领导，查询张无忌的员工信息</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> (salary,managerid)<span class="operator">=</span>(<span class="keyword">select</span> salary,managerid <span class="keyword">from</span> emp <span class="keyword">where</span> name<span class="operator">=</span><span class="string">&#x27;张无忌&#x27;</span>);</span><br></pre></td></tr></table></figure>
</li>
<li><p>（表子查询）</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 通过查询鹿杖容或者宋远桥的职位和薪资，查询员工信息</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> (job,salary) <span class="keyword">in</span> (<span class="keyword">select</span> job,salary <span class="keyword">from</span> emp <span class="keyword">where</span> name <span class="operator">=</span><span class="string">&#x27;鹿杖容&#x27;</span> <span class="keyword">or</span> name<span class="operator">=</span><span class="string">&#x27;宋远桥&#x27;</span>);</span><br></pre></td></tr></table></figure>
</li>
<li><p>带有EXISTS的谓语查询：</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- EXISTS的谓语查询不返回任何数据，只产生逻辑真值“true”，或逻辑假值“false”</span></span><br><span class="line"><span class="keyword">select</span> sname <span class="keyword">from</span> student <span class="keyword">where</span> <span class="keyword">exists</span>(<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> sc <span class="keyword">where</span> sno<span class="operator">=</span>student.sno <span class="keyword">and</span> cno<span class="operator">=</span><span class="string">&#x27;1&#x27;</span>);</span><br></pre></td></tr></table></figure></li>
</ul>
<h4 id="8-集合查询："><a href="#8-集合查询：" class="headerlink" title="8.集合查询："></a>8.集合查询：</h4><table>
<thead>
<tr>
<th>关键字</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>union</td>
<td>并操作（<strong>对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序</strong>）</td>
</tr>
<tr>
<td>union all</td>
<td>并操作（<strong>对两个结果集进行并集操作，包括重复行，不进行排序</strong>）</td>
</tr>
<tr>
<td>intersect</td>
<td>交操作</td>
</tr>
<tr>
<td>except</td>
<td>差操作</td>
</tr>
</tbody></table>
<ul>
<li><p>并操作：union</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> sdept<span class="operator">=</span><span class="string">&#x27;CS&#x27;</span> <span class="keyword">union</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> sage<span class="operator">&lt;=</span><span class="number">19</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>交操作：intersect</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> sdept<span class="operator">=</span><span class="string">&#x27;CS&#x27;</span> <span class="keyword">intersect</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> sage<span class="operator">&lt;=</span><span class="number">19</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>差操作：except</p>
 <figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> sdept<span class="operator">=</span><span class="string">&#x27;CS&#x27;</span> <span class="keyword">except</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> student <span class="keyword">where</span> sage<span class="operator">&lt;=</span><span class="number">19</span>;</span><br></pre></td></tr></table></figure></li>
</ul>
<h4 id="9-算术表达式："><a href="#9-算术表达式：" class="headerlink" title="9.算术表达式："></a>9.算术表达式：</h4><figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="meta prompt_"># </span><span class="language-bash">对数值型数据列、变量、常量可以使用算数操作符创建表达式（+ - * /）</span></span><br><span class="line"><span class="meta prompt_"># </span><span class="language-bash">对日期型数据列、变量、常量可以使用部分算数操作符创建表达式（+ -）</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 运算符不仅可以在列和常量之间进行运算，也可以在多列之间进行运算。</span></span><br><span class="line"><span class="keyword">select</span> last_name, salary, salary<span class="operator">*</span><span class="number">12</span> <span class="keyword">from</span> employees;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 补充：+说明</span></span><br><span class="line"><span class="comment">-- MySQL的+默认只有一个功能：运算符</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="number">100</span><span class="operator">+</span><span class="number">80</span>;      <span class="comment">-- 结果为180</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="string">&#x27;123&#x27;</span><span class="operator">+</span><span class="number">80</span>;    <span class="comment">-- 只要其中一个为数值，则试图将字符型转换成数值，转换成功做预算，结果为203</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="string">&#x27;abc&#x27;</span><span class="operator">+</span><span class="number">80</span>;    <span class="comment">-- 转换不成功，则字符型数值为0，结果为80</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="string">&#x27;This&#x27;</span><span class="operator">+</span><span class="string">&#x27;is&#x27;</span>; <span class="comment">-- 转换不成功，结果为0</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">NULL</span><span class="operator">+</span><span class="number">80</span>;     <span class="comment">-- 只要其中一个为NULL，则结果为NULL</span></span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">安全等于<span class="operator">&lt;=&gt;</span></span><br><span class="line"><span class="number">1.</span>可作为普通运算符的<span class="operator">=</span></span><br><span class="line"><span class="number">2.</span>也可以用于判断是否是<span class="keyword">NULL</span> </span><br><span class="line"><span class="keyword">where</span> salary <span class="keyword">is</span> <span class="keyword">NULL</span><span class="operator">/</span>(<span class="keyword">is</span> <span class="keyword">not</span> <span class="keyword">NULL</span>) <span class="operator">-</span><span class="operator">&gt;</span><span class="keyword">where</span> salary<span class="operator">&lt;=&gt;</span><span class="keyword">NULL</span></span><br><span class="line">示例<span class="number">1</span>：查询emp表奖金为空的员工信息。</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> comm <span class="operator">&lt;=&gt;</span> <span class="keyword">NULL</span>;</span><br><span class="line">示例<span class="number">2</span>：查询emp表奖金为<span class="number">50000</span>的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> comm <span class="operator">&lt;=&gt;</span> <span class="number">50000</span>;</span><br><span class="line"><span class="number">3.</span>用于判断表达式是否相等，相等返回<span class="number">1</span>，不相等返回<span class="number">0</span></span><br><span class="line">mysql<span class="operator">&gt;</span> <span class="keyword">select</span> <span class="number">1</span><span class="operator">+</span><span class="number">1</span> <span class="operator">&lt;=&gt;</span> <span class="number">3</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">1</span><span class="operator">+</span><span class="number">1</span> <span class="operator">&lt;=&gt;</span> <span class="number">3</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br><span class="line"><span class="operator">|</span>         <span class="number">0</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+</span></span><br></pre></td></tr></table></figure>



<h4 id="10-DQL编写顺序："><a href="#10-DQL编写顺序：" class="headerlink" title="10.DQL编写顺序："></a>10.DQL编写顺序：</h4><h2 id="10-SQL函数："><a href="#10-SQL函数：" class="headerlink" title="10.SQL函数："></a>10.SQL函数：</h2><ul>
<li>概念：是指一段可以直接被另一段程序调用的程序或代码</li>
<li>这里的函数是mysql的内置函数</li>
</ul>
<h3 id="1-聚合函数"><a href="#1-聚合函数" class="headerlink" title="1.聚合函数"></a>1.聚合函数</h3><ul>
<li>聚合函数需要和group by一起使用</li>
</ul>
<table>
<thead>
<tr>
<th>函数</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>count</td>
<td>计算个数</td>
</tr>
<tr>
<td>max</td>
<td>计算最大值</td>
</tr>
<tr>
<td>min</td>
<td>计算最小值</td>
</tr>
<tr>
<td>sum</td>
<td>计算和</td>
</tr>
<tr>
<td>avg</td>
<td>计算平均值</td>
</tr>
</tbody></table>
<ul>
<li><p>将一列数据作为整体，进行纵向计算</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(列名) <span class="keyword">from</span> 表名;</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(ifnull(列名,<span class="number">0</span>)) <span class="keyword">from</span> 表名;<span class="comment">---null替换为0</span></span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>
</li>
<li><p>注意：聚合函数排除null值</p>
</li>
</ul>
<h3 id="2-字符串函数："><a href="#2-字符串函数：" class="headerlink" title="2.字符串函数："></a>2.字符串函数：</h3><table>
<thead>
<tr>
<th>函数</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>concat( s1,s2…sn )</td>
<td>字符串拼接，将s1, s2, …, sn拼接成一个字符串</td>
</tr>
<tr>
<td>lower( str )</td>
<td>将字符串全部转为小写</td>
</tr>
<tr>
<td>upper( str )</td>
<td>将字符串全部转为大写</td>
</tr>
<tr>
<td>lpad( str,n,pad )</td>
<td>左填充，用字符串pad对str的左边进行填充，达到n个字符串长度</td>
</tr>
<tr>
<td>rpad( str,n,pad )</td>
<td>右填充，用字符串pad对str的右边进行填充，达到n个字符串长度</td>
</tr>
<tr>
<td>trim( str )</td>
<td>去掉字符串头部和尾部的空格</td>
</tr>
<tr>
<td>substring( str,start,len )</td>
<td>返回从字符串str从start位置起的len个长度的字符串</td>
</tr>
</tbody></table>
<p>使用示例：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 拼接</span></span><br><span class="line"><span class="keyword">SELECT</span> CONCAT(<span class="string">&#x27;Hello&#x27;</span>, <span class="string">&#x27;World&#x27;</span>);</span><br><span class="line"><span class="comment">-- 小写</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">LOWER</span>(<span class="string">&#x27;Hello&#x27;</span>);</span><br><span class="line"><span class="comment">-- 大写</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">UPPER</span>(<span class="string">&#x27;Hello&#x27;</span>);</span><br><span class="line"><span class="comment">-- 左填充</span></span><br><span class="line"><span class="keyword">SELECT</span> LPAD(<span class="string">&#x27;01&#x27;</span>, <span class="number">5</span>, <span class="string">&#x27;-&#x27;</span>);</span><br><span class="line"><span class="comment">-- 右填充</span></span><br><span class="line"><span class="keyword">SELECT</span> RPAD(<span class="string">&#x27;01&#x27;</span>, <span class="number">5</span>, <span class="string">&#x27;-&#x27;</span>);</span><br><span class="line"><span class="comment">-- 去除空格</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">TRIM</span>(<span class="string">&#x27; Hello World &#x27;</span>);</span><br><span class="line"><span class="comment">-- 切片（起始索引为1）</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">SUBSTRING</span>(<span class="string">&#x27;Hello World&#x27;</span>, <span class="number">1</span>, <span class="number">5</span>);</span><br></pre></td></tr></table></figure>



<h3 id="3-数值函数："><a href="#3-数值函数：" class="headerlink" title="3.数值函数："></a>3.数值函数：</h3><table>
<thead>
<tr>
<th align="left">函数</th>
<th align="left">功能</th>
</tr>
</thead>
<tbody><tr>
<td align="left">ceil( x )</td>
<td align="left">向上取整</td>
</tr>
<tr>
<td align="left">floor( x )</td>
<td align="left">向下取整</td>
</tr>
<tr>
<td align="left">mod( x,y )</td>
<td align="left">返回x&#x2F;y的模 (x%y)</td>
</tr>
<tr>
<td align="left">rand( )</td>
<td align="left">返回0~1内的随机数</td>
</tr>
<tr>
<td align="left">round( x,y )</td>
<td align="left">求参数x的四舍五入值，保留y位小数</td>
</tr>
<tr>
<td align="left">abs(x)</td>
<td align="left">求绝对值</td>
</tr>
<tr>
<td align="left">sqrt</td>
<td align="left">求平方根</td>
</tr>
<tr>
<td align="left">pow &#x2F; power</td>
<td align="left">两个函数的功能相同，返回参数的幂次方</td>
</tr>
<tr>
<td align="left">mod</td>
<td align="left">求余数</td>
</tr>
</tbody></table>
<p>使用示例：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 向上取整</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">CEIL</span>(<span class="number">2.5</span>);</span><br><span class="line"><span class="comment">-- 返回0~1内的随机数</span></span><br><span class="line"><span class="keyword">SELECT</span> rand();</span><br><span class="line"><span class="comment">-- 返回x/y的模</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">MOD</span>(<span class="number">5</span>,<span class="number">3</span>);</span><br></pre></td></tr></table></figure>



<h3 id="4-日期函数："><a href="#4-日期函数：" class="headerlink" title="4.日期函数："></a>4.日期函数：</h3><table>
<thead>
<tr>
<th align="left">函数</th>
<th align="left">功能</th>
</tr>
</thead>
<tbody><tr>
<td align="left">curdate( )</td>
<td align="left">返回当前日期（2022-05-28）</td>
</tr>
<tr>
<td align="left">curtime( )</td>
<td align="left">返回当前时间（16:17:46）</td>
</tr>
<tr>
<td align="left">now( )</td>
<td align="left">返回当前日期和时间（2022-05-28 16:19:09）</td>
</tr>
<tr>
<td align="left">year( date )</td>
<td align="left">获取指定date的年份（2022）</td>
</tr>
<tr>
<td align="left">month( date )</td>
<td align="left">获取指定date的月份</td>
</tr>
<tr>
<td align="left">day( date )</td>
<td align="left">获取指定date的日期</td>
</tr>
<tr>
<td align="left">DATE_ADD(date, INTERVAL expr type)</td>
<td align="left">返回一个日期&#x2F;时间值+上一个时间间隔expr后的时间值</td>
</tr>
<tr>
<td align="left">datediff( date1,date2 )</td>
<td align="left">返回起始时间date1和结束时间date2之间的天数（date1-date2）</td>
</tr>
</tbody></table>
<p>使用示例：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 返回当前日期</span></span><br><span class="line"><span class="keyword">SELECT</span> CURDATE();</span><br><span class="line"><span class="comment">-- 返回当前时间</span></span><br><span class="line"><span class="keyword">SELECT</span> CURTIME();</span><br><span class="line"><span class="comment">-- 返回当前日期和时间</span></span><br><span class="line"><span class="keyword">SELECT</span> NOW();</span><br><span class="line"><span class="comment">-- 获取指定date的年份</span></span><br><span class="line"><span class="keyword">SELECT</span> <span class="keyword">YEAR</span>(NOW());</span><br><span class="line"><span class="comment">-- 返回一个日期/时间值加上一个时间间隔expr后的时间值（ 2092-05-28 16:21:49 ）</span></span><br><span class="line"><span class="keyword">SELECT</span> DATE_ADD(NOW(), <span class="type">INTERVAL</span> <span class="number">70</span> <span class="keyword">YEAR</span>);</span><br><span class="line"><span class="comment">-- 返回起始时间date1和结束时间date2之间的天数（ -70 ）</span></span><br><span class="line"><span class="keyword">SELECT</span> DATEDIFF(NOW(),DATE_ADD(NOW(), <span class="type">INTERVAL</span> <span class="number">70</span> <span class="keyword">DAY</span>));</span><br></pre></td></tr></table></figure>



<h3 id="5-流程函数："><a href="#5-流程函数：" class="headerlink" title="5.流程函数："></a>5.流程函数：</h3><table>
<thead>
<tr>
<th align="left">函数</th>
<th align="left">功能</th>
</tr>
</thead>
<tbody><tr>
<td align="left">if( value,t,f )</td>
<td align="left">如果value为true，则返回t，否则返回f</td>
</tr>
<tr>
<td align="left">ifnull(value1, value2)</td>
<td align="left">如果value1不为空，返回value1，否则返回value2</td>
</tr>
<tr>
<td align="left">case when [ val1 ] then [ res1 ] … else [ default ] end</td>
<td align="left">如果val1为true，返回res1，否则返回default默认值</td>
</tr>
<tr>
<td align="left">case [ expr ] when [ val1 ] then [ res1 ] … else [ default ] end</td>
<td align="left">如果expr的值等于val1，返回res1， 否则返回default默认值</td>
</tr>
</tbody></table>
<p>例子：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 如果value为true，则返回t，否则返回f</span></span><br><span class="line"><span class="keyword">SELECT</span> IF(<span class="literal">TRUE</span>,<span class="string">&#x27;hello&#x27;</span>,<span class="string">&#x27;yes&#x27;</span>);</span><br><span class="line"><span class="comment">-- 如果val1为true，返回res1，否则返回default默认值</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">    name,</span><br><span class="line">    (<span class="keyword">case</span> <span class="keyword">when</span> age <span class="operator">&gt;</span> <span class="number">30</span> <span class="keyword">then</span> <span class="string">&#x27;中年&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;青年&#x27;</span> <span class="keyword">end</span>)</span><br><span class="line"><span class="keyword">from</span> employee;</span><br><span class="line"><span class="comment">-- 如果expr的值等于val1，返回res1， 否则返回default默认值</span></span><br><span class="line"><span class="keyword">select</span></span><br><span class="line">    name,</span><br><span class="line">    (<span class="keyword">case</span> workaddress <span class="keyword">when</span> <span class="string">&#x27;北京市&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;一线城市&#x27;</span> <span class="keyword">when</span> <span class="string">&#x27;上海市&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;一线城市&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;二线城市&#x27;</span> <span class="keyword">end</span>) <span class="keyword">as</span> <span class="string">&#x27;工作地址&#x27;</span></span><br><span class="line"><span class="keyword">from</span> employee;</span><br></pre></td></tr></table></figure>





<h2 id="11-级联操作"><a href="#11-级联操作" class="headerlink" title="11.级联操作"></a>11.级联操作</h2><table>
<thead>
<tr>
<th align="left">行为</th>
<th align="left">说明</th>
</tr>
</thead>
<tbody><tr>
<td align="left">no action</td>
<td align="left">当在父表中删除&#x2F;更新时，首先检查是否有对应外键，如果有则不允许删除&#x2F;更新（与restrict一致）</td>
</tr>
<tr>
<td align="left">restrict</td>
<td align="left">当在父表中删除&#x2F;更新时，首先检查否有对应外键，如果有则不允许删除&#x2F;更新（与no action一致）</td>
</tr>
<tr>
<td align="left">cascade</td>
<td align="left">当在父表中删除&#x2F;更新时，首先检查是否有对应外键，如果有则也删除&#x2F;更新外键在子表中的记录</td>
</tr>
<tr>
<td align="left">set null</td>
<td align="left">当在父表中删除&#x2F;更新时，首先检查是否有对应外键，如果有则设置子表中该外键值为null（要求该外键允许为null）</td>
</tr>
<tr>
<td align="left">set default</td>
<td align="left">父表有变更时，子表将外键设为一个默认值（Innodb不支持）</td>
</tr>
</tbody></table>
<ul>
<li><p>概念：<strong>修改数据（外键和连接的主键）</strong></p>
</li>
<li><p>添加外键时，添加级联操作或删除级联操作：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> <span class="keyword">constraint</span> 外键名 <span class="keyword">foreign</span> key(外键列名) <span class="keyword">references</span> 表名<span class="number">2</span>（主键<span class="number">2</span>) <span class="keyword">on</span> <span class="keyword">update</span> cascade;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">table</span> 表名 <span class="keyword">add</span> <span class="keyword">constraint</span> 外键名 <span class="keyword">foreign</span> key(外键列名) <span class="keyword">references</span> 表名<span class="number">2</span>（主键<span class="number">2</span>) <span class="keyword">on</span> <span class="keyword">delete</span> cascade;</span><br></pre></td></tr></table></figure>
</li>
<li><p>级联更新：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">on</span> <span class="keyword">update</span> cascade</span><br></pre></td></tr></table></figure>
</li>
<li><p>级联删除：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">on</span> <span class="keyword">delete</span> cascade</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="12-数据库的备份和还原："><a href="#12-数据库的备份和还原：" class="headerlink" title="12.数据库的备份和还原："></a>12.数据库的备份和还原：</h2><h3 id="1-MySQL日志管理："><a href="#1-MySQL日志管理：" class="headerlink" title="1.MySQL日志管理："></a>1.MySQL日志管理：</h3><ul>
<li>在数据库保存数据时，有时候不可避免会出现数据丢失或者被破坏，这样情况下，我们必须保证数据的安全性和完整性，就需要使用日志来查看或者恢复数据了。</li>
<li>数据库中数据丢失或被破坏可能原因：<ul>
<li>误删除数据库</li>
<li>数据库工作时，意外断电或程序意外终止</li>
<li>由于病毒造成的数据库损坏或丢失</li>
<li>文件系统损坏后，系统进行自检操作</li>
<li>升级数据库时，命令语句不严格</li>
<li>设备故障等等</li>
<li>自然灾害</li>
<li>盗窃</li>
</ul>
</li>
</ul>
<h3 id="2-MySQL日志类型："><a href="#2-MySQL日志类型：" class="headerlink" title="2.MySQL日志类型："></a>2.MySQL日志类型：</h3><ul>
<li><p>MySQL有几个不同的日志文件，可以帮助你找出mysqld内部发生的事情：</p>
<table>
<thead>
<tr>
<th>日志类型</th>
<th>记入文件中的信息类型</th>
</tr>
</thead>
<tbody><tr>
<td>错误日志</td>
<td>记录启动、运行或停止时出现的问题</td>
</tr>
<tr>
<td>查询日志</td>
<td>记录建立的客户端连接和执行的语句</td>
</tr>
<tr>
<td>二进制日志</td>
<td>记录所有更改数据的语句。主要用于复制和即时点恢复</td>
</tr>
<tr>
<td>慢日志</td>
<td>记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询</td>
</tr>
<tr>
<td>事务日志</td>
<td>记录InnoDB等支持事务的存储引擎执行事务时产生的日志</td>
</tr>
</tbody></table>
</li>
<li><p>在mysql中，执行SQL语句：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql&gt; flush logs</span><br></pre></td></tr></table></figure>
</li>
<li><p>在shell中，通过mysqladmin命令执行日志刷新：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">mysqladmin flush-logs</span><br><span class="line">mysqladmin refresh</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="3-错误日志："><a href="#3-错误日志：" class="headerlink" title="3.错误日志："></a>3.错误日志：</h3><ul>
<li><p>错误日志主要记录如下几种日志：</p>
<ul>
<li>服务器启动和关闭过程中的信息</li>
<li>服务器运行过程中的错误信息</li>
<li>事件调度器运行一个时间是产生的信息</li>
<li>在从服务器上启动从服务器进程是产生的信息</li>
</ul>
</li>
<li><p>查看当前错误日志配置：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">show global variables like &#x27;%log_error%&#x27;;</span><br><span class="line">show global variables like &#x27;%log_warnings%&#x27;;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="4-通用查询日志："><a href="#4-通用查询日志：" class="headerlink" title="4.通用查询日志："></a>4.通用查询日志：</h3><ul>
<li><p>字段说明：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="operator">-</span> 启动开关：general_log<span class="operator">=</span>&#123;<span class="keyword">ON</span><span class="operator">|</span>OFF&#125;</span><br><span class="line"><span class="operator">-</span> 日志文件变量：general_log_file[<span class="operator">=</span><span class="operator">/</span>PATH<span class="operator">/</span><span class="keyword">TO</span><span class="operator">/</span>file]</span><br><span class="line"><span class="operator">-</span> 全局日志开关：log<span class="operator">=</span>&#123;<span class="keyword">ON</span><span class="operator">|</span>OFF&#125; 该开关打开后，所有日志都会被启用</span><br><span class="line"><span class="operator">-</span> 记录类型：log_output<span class="operator">=</span>&#123;<span class="keyword">TABLE</span><span class="operator">|</span>FILE<span class="operator">|</span><span class="keyword">NONE</span>&#125;：</span><br></pre></td></tr></table></figure>
</li>
<li><p>因此，要启用通用查询日志，需要至少配置general_log&#x3D;ON，log_output&#x3D;{TABLE|FILE}。而general_log_file如果没有指定，默认名是host_name.log。</p>
</li>
<li><p>看看上述几个值的默认配置：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">global</span> variables <span class="keyword">like</span> <span class="string">&#x27;%general_log%&#x27;</span>;</span><br><span class="line"><span class="keyword">show</span> <span class="keyword">global</span> variables <span class="keyword">like</span> <span class="string">&#x27;%log_output%&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> general_log<span class="operator">=</span><span class="keyword">NO</span>;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="5-慢查询日志："><a href="#5-慢查询日志：" class="headerlink" title="5.慢查询日志："></a>5.慢查询日志：</h3><ul>
<li><p>MySQL如果启用了 slow_query_log&#x3D;ON 选项，就会记录执行时间超过long_query_time的查询（初使表锁定的时间不算作执行时间）。日志记录文件为slow_query_log_file[&#x3D;file_name]，如果没有给出file_name值， 默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。</p>
</li>
<li><p>查看慢查询日志：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 默认没有启用慢查询，为了服务器调优，建议开启</span><br><span class="line"><span class="keyword">show</span> <span class="keyword">global</span> variables <span class="keyword">like</span> <span class="string">&#x27;%slow_query_log%&#x27;</span>;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"># 开启方法,当前生效，永久有效配置文件中设置</span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> slow_query_log<span class="operator">=</span><span class="keyword">ON</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>开启满查询日志：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> slow_query_log<span class="operator">=</span><span class="keyword">ON</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看慢查询查询时长超时值：（默认10秒）</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">global</span> variables <span class="keyword">like</span> <span class="string">&#x27;long_query_time&#x27;</span>;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="6-二进制日志："><a href="#6-二进制日志：" class="headerlink" title="6.二进制日志："></a>6.二进制日志：</h3><ul>
<li><p>二进制日志（BINLOG）记录了所有的<strong>DDL</strong>（数据定义语言，对表的操作）语言和<strong>DML</strong>（数操纵语言，对数据的增删改查）语言，但不包括数据查询（select，show）语句。</p>
</li>
<li><p>作用：</p>
<ol>
<li>灾难时的数据恢复；2.MySQL的主从复制</li>
</ol>
</li>
<li><p>默认二进制日志时开启着的，参看二进制文件的存放目录：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> variables <span class="keyword">like</span> <span class="string">&#x27;%log_bin%&#x27;</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>开启日志：</p>
<ul>
<li><p>二进制日志启动开关：（默认开启）</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">set global log_bin=NO;</span><br></pre></td></tr></table></figure>
</li>
<li><p>注意：在5.6及以上版本一定要手动指定。5.6以下版本默认file_name为$datadir&#x2F;mysqld-binlog。</p>
</li>
<li><p>查看二进制日志的工具为：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqlbinlog</span><br></pre></td></tr></table></figure>
</li>
<li><p>二进制日志包含了所有更新了数据或者已经潜在更新了数据（例如，没有匹配任何行的一个DELETE）的所有语句。</p>
</li>
</ul>
</li>
<li><p>二进制日志的管理：</p>
<ul>
<li><p>日志滚动。在my.cnf中设定max_binlog_size &#x3D; 200M，表示限制二进制日志最大尺寸为200M，超过200M后进行滚动。</p>
</li>
<li><p>MySQL的滚动方式与其他日志不太一样，滚动时会创建一个新的编号大1的日志用于记录最新的日志，而原日志名字不会被改变。每次重启MySQL服务，日志都会自动滚动一次。</p>
</li>
<li><p>另外如果需要手动滚动，则使用命令 </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">flush logs</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>日志查看：</p>
<ul>
<li><p>查看有哪些二进制日志文件：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="type">binary</span> logs;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看当前正在使用的是哪一个二进制日志文件：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> master status;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看二进制日志内容：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> binlog events <span class="keyword">in</span> <span class="string">&#x27;binlog.000002&#x27;</span>;</span><br><span class="line"><span class="keyword">show</span> binlog events <span class="keyword">in</span> <span class="string">&#x27;binlog.000002&#x27;</span>\G;    <span class="comment">-- 键值对显示</span></span><br><span class="line"><span class="keyword">show</span> binlog events <span class="keyword">in</span> <span class="string">&#x27;binlog.000002&#x27;</span> <span class="keyword">from</span> <span class="number">203</span>;    <span class="comment">-- 指定显示从哪个Position（位置）开始</span></span><br></pre></td></tr></table></figure>
</li>
<li><p>使用命令mysqlbinlog查看二进制日志内容：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqlbinlog [options] log<span class="operator">-</span>files</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>日志删除：</p>
</li>
<li><p>清空所有的binlog日志：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">reset master;</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除指定日志的之前日志：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">purge [master/binary] logs to &#x27;binlog.000004&#x27;;</span><br></pre></td></tr></table></figure>


</li>
<li><p>二进制日志还原数据：</p>
<ul>
<li><p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqlbinlog [option] filename <span class="operator">|</span> mysql <span class="operator">-</span>uuser <span class="operator">-</span>ppassword</span><br></pre></td></tr></table></figure>

<table>
<thead>
<tr>
<th>option</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>–start-datetime、–stop-datetime</td>
<td>能够指定恢复数据库的起始时间点和结束时间点</td>
</tr>
<tr>
<td>–start-position、–stop–position</td>
<td>能够指定恢复数据的开始位置和结束位置</td>
</tr>
</tbody></table>
</li>
<li><p>使用：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqlbinlog --stop-position=425 binlog.000010 | mysql-uroot -pAdmin123!</span><br></pre></td></tr></table></figure>
</li>
<li><p>使用mysqlbinlog读取需要的日志内容，使用标准输入重定向到一个sql文件，然后在mysql服务器上导入即可，如下：</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysqlbinlog binlog.000001 &gt; /usr/qv123/temp_date.sql</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
</ul>
<h3 id="7-MySQL的备份："><a href="#7-MySQL的备份：" class="headerlink" title="7.MySQL的备份："></a>7.MySQL的备份：</h3><ul>
<li><p>备份类型：</p>
<ul>
<li>根据服务器状态，可以分为热备份、温备份、冷备份<ul>
<li>热备份：读、写不受影响；</li>
<li>温备份：仅可以执行读操作；</li>
<li>冷备份：离线备份；读、写操作均中止；</li>
</ul>
</li>
<li>注：为保证所备份数据的完整性，在停止mysql数据库之前，需要先执行flush tables语句将所有数据写入到数据文件的文本文件里。</li>
<li>从对象来分，可以分为物理备份与逻辑备份<ul>
<li>物理备份：复制数据文件；</li>
<li>逻辑备份：将数据导出至文本文件中，内容是一条条sql语句，或者是表内实际数据组成；</li>
</ul>
</li>
<li>从数据收集来分，可以完全备份、增量备份、差异备份<ul>
<li>完全备份：备份全部数据；</li>
<li>增量备份：仅备份上次完全备份或增量备份以后变化的数据；</li>
<li>差异备份：仅备份上次完全备份以来变化的数据；</li>
</ul>
</li>
</ul>
</li>
<li><p>逻辑备份优缺点：</p>
<ul>
<li>逻辑备份的优点：<ul>
<li>在备份速度上两种备份要取决于不同的存储引擎。</li>
<li>物理备份的还原速度非常快。但是物理备份的最小粒度只能做到表。</li>
<li>逻辑备份保存的结构通常都是纯ASCII的，所以我们可以使用文本处理工具来处理。</li>
<li>逻辑备份有非常强的兼容性，而物理备份则对版本要求非常高。</li>
<li>逻辑备份也对保持数据的安全性有保证。</li>
</ul>
</li>
<li>逻辑备份的缺点：<ul>
<li>逻辑备份要对RDBMS产生额外的压力，而裸备份无压力。</li>
<li>逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩</li>
<li>逻辑备份可能会丢失浮点数的精度信息。</li>
</ul>
</li>
</ul>
</li>
<li><p>备份内容：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">数据文件</span><br><span class="line">日志文件（比如事务日志，二进制日志）</span><br><span class="line">存储过程，存储函数，触发器</span><br><span class="line">配置文件（十分重要，各个配置文件都要备份）</span><br><span class="line">用于实现数据库备份的脚本，数据库自身清理的Crontab等……</span><br></pre></td></tr></table></figure>
</li>
<li><p>MySQL自带的备份工具：</p>
<ul>
<li><p>mysqldump，是mysql数据库管理系统自带的逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢。但是在实现还原的时候，具有很大的操作余地，具有很好的弹性。</p>
</li>
<li><p>备份某库的某表，如果有多张表，用空格隔开</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql8-0-30 mysql]# mysqldump -u[用户] -p[密码] [数据库] [表] &gt; [路径]     # 备份到指定文件中</span><br></pre></td></tr></table></figure>
</li>
<li><p>备份某库的里面的所有表</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql8-0-30 mysql]# mysqldump -u[用户] -p[密码] [数据库] &gt; [路径]</span><br></pre></td></tr></table></figure>
</li>
<li><p>还原备份</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql8-0-30 mysql]# mysql -u[用户] -p[密码] [数据库] &lt; [路径]              # 还原指定文件中的备份</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql <span class="operator">&gt;</span> source [路径]                                        # 先使用数据库，还原指定文件中的备份</span><br></pre></td></tr></table></figure>
</li>
<li><p>备份数据库和该库里面所有的表，指定多个库时用空格隔开</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql8-0-30 mysql]# mysqldump -u[用户] -p[密码] --databases [数据库] &gt; [路径]</span><br></pre></td></tr></table></figure>
</li>
<li><p>备份所有数据库</p>
<figure class="highlight shell"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[root@mysql8-0-30 mysql]# mysqldump -u[用户] -p[密码] --all-databases &gt; [路径]</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>mysqlhotcopy 物理备份工具</p>
<ul>
<li>只支持MyISAM引擎。基本上属于冷备的范畴，物理备份，速度比较快。</li>
<li>多用于mysql5.5之前。mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份）</li>
<li>本质是使用锁表语句，然后cp或scp。</li>
</ul>
</li>
<li><p>文件系统备份工具：</p>
<ul>
<li>cp命令， 冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令对其进行备份的备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。</li>
<li>lvm 几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，恢复速度也比较快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用。而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。</li>
</ul>
</li>
<li><p>其它工具：</p>
<ul>
<li>ibbackup：商业工具 MyISAM是温备份，InnoDB是热备份 ，备份和还原速度都很快，这个软件它的每服务器授权版本是5000美元。</li>
<li>xtrabackup：开源工具 MyISAM是温备份，InnoDB是热备份 ，是ibbackup商业工具的替代工具。</li>
<li>mysqlbackup：ORACLE公司也提供了针对企业的备份软件MySQL Enterprise Backup简称：</li>
</ul>
</li>
</ul>
<h2 id="13-MySQL视图："><a href="#13-MySQL视图：" class="headerlink" title="13.MySQL视图："></a>13.MySQL视图：</h2><h3 id="1-视图的概念："><a href="#1-视图的概念：" class="headerlink" title="1.视图的概念："></a>1.视图的概念：</h3><ul>
<li><p><strong>视图（View）是一种虚拟存在的表</strong>。视图中的数据并不在数据库中真实存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动他生成的。</p>
</li>
<li><p>在SQL语言中的视图View是数据库的<strong>外模式</strong></p>
</li>
<li><p>视图是一种常用的数据库对象，它是提供<strong>查看</strong>和<strong>存放</strong>数据的另一种途径，可以简化数据库的操作</p>
</li>
<li><p>视图通过以定制的方式显示来自一个或多个表的数据</p>
</li>
<li><p>视图是一种数据库对象，用户可以像查询普通表一样查询视图</p>
</li>
<li><p>视图内其实没有存储任何数据，它只是对表的一个查询</p>
</li>
<li><p>视图的定义保存在数据字典内，创建视图所基于的表称为“基表”</p>
</li>
<li><p>如果修改原视图或者原表数据，视图会同步数据；如果修改视图数据，原视图或者原表不会发生变化</p>
</li>
<li><p>视图的作用和优点：</p>
<ul>
<li><p>作用：</p>
<ul>
<li>控制安全</li>
<li>保存查询数据</li>
</ul>
</li>
<li><p>优点：</p>
<ul>
<li>提供了灵活一致级别安全性。</li>
<li>隐藏了数据的复杂性</li>
<li>简化了用户的SQL指令</li>
<li>通过重命名列，从另一个角度提供数据</li>
</ul>
</li>
</ul>
</li>
<li><p>视图使用规则：</p>
<ul>
<li>视图必须有唯一命名</li>
<li>在mysql中视图的数量没有限制</li>
<li>创建视图必须从管理员那里获得必要的权限</li>
<li>视图支持嵌套，也就是说可以利用其他视图检索出来的数据创建新的视图</li>
<li>在视图中可以使用OREDR BY，但是如果视图内已经使用该排序子句，则视图的ORDER BY将覆盖前面的ORDER BY。</li>
<li>视图不能索引，也不能关联触发器或默认值</li>
<li>视图可以和表同时使用</li>
</ul>
</li>
</ul>
<h3 id="2-建立视图："><a href="#2-建立视图：" class="headerlink" title="2.建立视图："></a>2.建立视图：</h3><ul>
<li><p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> [<span class="keyword">or</span> replace] [视图名] <span class="keyword">as</span> <span class="keyword">select</span> [列名...] <span class="keyword">from</span> 表名 <span class="keyword">where</span> [条件] [<span class="keyword">with</span> <span class="keyword">cascaded</span> <span class="keyword">check</span> option]</span><br><span class="line">例：</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> s_address <span class="keyword">as</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t_address;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> s_address(id,name,<span class="type">time</span>) <span class="keyword">as</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t_address;</span><br><span class="line"><span class="comment">-- or replace（表示替换，可以省略）</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> 视图名 <span class="keyword">as</span> <span class="keyword">select</span> 列名... <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件    </span><br><span class="line"><span class="comment">-- with check option视图的检查选项,MySQL会通过视图检查正在更改的每个行,插入，更新，删除，以使其符合视图的定义</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> 视图名 <span class="keyword">as</span> <span class="keyword">select</span> 列名... <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件 <span class="keyword">with</span> <span class="keyword">check</span> option</span><br></pre></td></tr></table></figure>
</li>
<li><p>MySQL基于另一个视图创建视图，，他会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项：cascaded和local，默认为cascaded</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> 视图名<span class="number">2</span> <span class="keyword">as</span> <span class="keyword">select</span> 列名... <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件 <span class="keyword">with</span> <span class="keyword">cascaded</span> <span class="keyword">check</span> option</span><br><span class="line"><span class="comment">-- 因为视图1依据视图2而生，又因为视图2添加了检查选项，所以视图1也默认添加</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> 视图名<span class="number">1</span> <span class="keyword">as</span> <span class="keyword">select</span> 列名... <span class="keyword">from</span> 视图名<span class="number">2</span> <span class="keyword">where</span> 条件</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> 视图名<span class="number">2</span> <span class="keyword">as</span> <span class="keyword">select</span> 列名... <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件 <span class="keyword">with</span> <span class="keyword">local</span> <span class="keyword">check</span> option</span><br><span class="line"><span class="comment">--  不检查视图1</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">or</span> replace <span class="keyword">view</span> 视图名<span class="number">1</span> <span class="keyword">as</span> <span class="keyword">select</span> 列名... <span class="keyword">from</span> 视图名<span class="number">2</span> <span class="keyword">where</span> 条件</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="2-更新视图："><a href="#2-更新视图：" class="headerlink" title="2.更新视图："></a>2.更新视图：</h3><ul>
<li><p>方式一：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> s_address(id,name,<span class="type">time</span>) <span class="keyword">as</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t_address;</span><br><span class="line"><span class="keyword">create</span> <span class="keyword">view</span> 视图名 <span class="keyword">as</span> <span class="keyword">select</span> 列名... <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件</span><br></pre></td></tr></table></figure>
</li>
<li><p>方式二：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">view</span> s_address(id,name,<span class="type">time</span>) <span class="keyword">as</span> <span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> t_address;</span><br><span class="line"><span class="keyword">alter</span> <span class="keyword">view</span> 视图名 <span class="keyword">as</span> <span class="keyword">select</span> 列名... <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="4-删除视图："><a href="#4-删除视图：" class="headerlink" title="4.删除视图："></a>4.删除视图：</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">view</span> 视图名 </span><br><span class="line"><span class="comment">-- cascade删除视图1和由它所导出的所有视图</span></span><br><span class="line"><span class="keyword">drop</span> <span class="keyword">view</span> 视图名 cascade</span><br></pre></td></tr></table></figure>

<h3 id="5-查询视图："><a href="#5-查询视图：" class="headerlink" title="5.查询视图："></a>5.查询视图：</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- 查看创建视图的语句</span></span><br><span class="line"><span class="keyword">show</span> <span class="keyword">create</span> <span class="keyword">view</span> 视图名称;</span><br><span class="line"><span class="comment">-- 查看视图数据</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 视图......;</span><br></pre></td></tr></table></figure>

<h3 id="6-更新数据："><a href="#6-更新数据：" class="headerlink" title="6.更新数据："></a>6.更新数据：</h3><ul>
<li><p>概述：由于视图是不实际存储数据的虚表，因此对视图的更新最终要转换为对基本表的更新</p>
</li>
<li><p>要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项，则该视图不可更新：</p>
<ol>
<li>聚合函数或窗口函数（SUM()，MIN()，MAX()，COUNT()等）</li>
<li>DISTINCT</li>
<li>GROUP BY</li>
<li>HAVING</li>
<li>UNION 或者 UNION ALL</li>
</ol>
</li>
<li><p>插入insert</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span> <span class="keyword">into</span> 视图名 <span class="keyword">values</span>(值<span class="number">1</span>,....);</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除delete</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> 视图名 <span class="keyword">where</span> 条件</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改update</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> 视图名 <span class="keyword">set</span> 视图列<span class="number">1</span><span class="operator">=</span>新值 <span class="keyword">where</span> 视图列<span class="number">2</span><span class="operator">=</span>值;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="7-视图的作用："><a href="#7-视图的作用：" class="headerlink" title="7.视图的作用："></a>7.视图的作用：</h3><ol>
<li>视图能<strong>简化</strong>用户的操作</li>
<li>视图使用户能以多种角度看待同一数据</li>
<li>视图对重构数据库提供了一定程度的<strong>逻辑独立性</strong></li>
<li>视图能够对机密数据提供<strong>安全</strong>保护</li>
<li>适当利用视图可以清晰地表达查询</li>
</ol>
<h2 id="14-MySQL索引："><a href="#14-MySQL索引：" class="headerlink" title="14.MySQL索引："></a>14.MySQL索引：</h2><h3 id="1-索引的概述："><a href="#1-索引的概述：" class="headerlink" title="1.索引的概述："></a>1.索引的概述：</h3><ul>
<li><p>索引是帮助 MySQL <strong>高效获取数据</strong>的<strong>数据结构（有序）</strong>。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查询算法，这种数据结构就是索引。</p>
</li>
<li><p>优缺点：</p>
<ul>
<li><strong>优点：</strong><ul>
<li>提高数据检索效率，降低数据库的IO成本</li>
<li>通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗</li>
</ul>
</li>
<li><strong>缺点：</strong><ul>
<li>索引列也是要占用空间的</li>
<li>索引大大提高了查询效率，但降低了更新的速度，比如 INSERT、UPDATE、DELETE</li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="2-B树："><a href="#2-B树：" class="headerlink" title="2.B树："></a>2.B树：</h3><ul>
<li><p>B树是一种树状数据结构，是一种二叉查找树,也称多路并衡二叉树；这种数据结构中，一个结点允许多于两个key的存在。</p>
</li>
<li><p>它能够存储数据、对其进行排序并允许以O(logn)的时间复杂度进行查找、顺序读取、插入和删除等操作。</p>
</li>
<li><p>B树的特性：</p>
<ul>
<li>每个结点最多有M-1个key，<strong>并且以升序排列</strong>；</li>
<li>每个结点最多能有M个子结点；</li>
<li>根结点至少有两个子结点；</li>
</ul>
</li>
<li><p>在实际应用中B树的阶数一般都比较大（通常大于100），所以，即使存储大量的数据，B树的高度仍然比较小，这</p>
<p>样在某些应用场景下，就可以体现出它的优势。</p>
</li>
<li><p>B树存储数据：</p>
<p><img src="C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20230214133056999.png" alt="image-20230214133056999"></p>
</li>
<li><p>B树在磁盘文件中的应用：</p>
<ul>
<li><p>在我们的程序中，不可避免的需要通过IO操作文件，而我们的文件是存储在磁盘上的。计算机操作磁盘上的文件是</p>
<p>通过文件系统进行操作的，在文件系统中就使用到了B树这种数据结构。</p>
</li>
</ul>
</li>
</ul>
<h3 id="3-B-树："><a href="#3-B-树：" class="headerlink" title="3.B+树："></a>3.B+树：</h3><ul>
<li><p>B+树是对B树的一种变形树，它与B树的差异在于：</p>
<ul>
<li>非叶结点仅具有索引作用，也就是说，<strong>非叶子结点只存储key，不存储value；</strong></li>
<li><strong>树的所有叶子结点构成一个有序链表</strong>，可以按照key排序的次序遍历全部数据。</li>
</ul>
</li>
<li><p>B+树存储数据：</p>
<ul>
<li><p>若参数M选择为5，那么每个结点最多包含4个键值对，我们以5阶B+树为例，看看B+树的数据存储。</p>
<p><img src="C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20230214133310429.png" alt="image-20230214133310429"></p>
</li>
</ul>
</li>
<li><p>B树和B+树的对比：</p>
<ul>
<li><p>B树的优点：</p>
<p>由于B树的每一个节点都包含key和value，因此我们根据key查找value时，只需要找到key所在的位置，就能找到value，但B+树只有叶子结点存储数据，索引每一次查找，都必须一次一次，一直找到树的最大深度处，也就是叶子结点的深度，才能找到value。</p>
</li>
<li><p>B+树的优点：</p>
<p>1.由于B+树在非叶子结点上不包含真正的数据，只当做索引使用，因此在内存相同的情况下，能够存放更多的</p>
<p>key。 2.B+树的叶子结点都是相连的，因此对整棵树的遍历只需要一次线性遍历叶子结点即可。而且由于数据顺序</p>
<p>排列并且相连，所以便于区间查找和搜索。而B树则需要进行每一层的递归遍历</p>
</li>
</ul>
</li>
<li><p>B+树在数据库中的应用：</p>
<ul>
<li><p>在数据库的操作中，查询操作可以说是最频繁的一种操作，因此在设计数据库时，必须要考虑到查询的效率问题，</p>
<p>在很多数据库中，都是用到了B+树来提高查询的效率；</p>
</li>
<li><p><strong>未建立主键索引查询</strong>：</p>
<img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220619111356853.png" alt="image-20220619111356853" style="zoom: 67%;" />
</li>
<li><p><strong>建立主键索引查询</strong>：</p>
<img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220619111523426.png" alt="image-20220619111523426" style="zoom:67%;" />
</li>
<li><p><strong>区间查询</strong>：</p>
<ul>
<li>执行 select * from user where id&gt;&#x3D;12 and id&lt;&#x3D;18 ,如果有了索引，由于B+树的叶子结点形成了一个有序链表，所以我们只需要找到id为12的叶子结点，按照遍历链表的方式顺序往后查即可，效率非常高。</li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="4-索引的结构："><a href="#4-索引的结构：" class="headerlink" title="4.索引的结构："></a>4.索引的结构：</h3><table>
<thead>
<tr>
<th align="left">索引结构</th>
<th align="left">描述</th>
</tr>
</thead>
<tbody><tr>
<td align="left">B+Tree</td>
<td align="left">最常见的索引类型，大部分引擎都支持B+树索引</td>
</tr>
<tr>
<td align="left">Hash</td>
<td align="left">底层数据结构是用哈希表实现，<strong>只有精确匹配</strong>索引列的查询才有效，<strong>不支持范围查询</strong></td>
</tr>
<tr>
<td align="left">R-Tree(空间索引)</td>
<td align="left">空间索引是 MyISAM 引擎的一个特殊索引类型，主要用于<strong>地理空间数据类型</strong>，通常使用较少</td>
</tr>
<tr>
<td align="left">Full-Text(全文索引)</td>
<td align="left">是一种通过建立倒排索引，快速匹配文档的方式，类似于 Lucene, Solr, ES</td>
</tr>
</tbody></table>
<table>
<thead>
<tr>
<th align="left">索引</th>
<th align="left">InnoDB</th>
<th align="left">MyISAM</th>
<th align="left">Memory</th>
</tr>
</thead>
<tbody><tr>
<td align="left">B+Tree索引</td>
<td align="left">支持</td>
<td align="left">支持</td>
<td align="left">支持</td>
</tr>
<tr>
<td align="left">Hash索引</td>
<td align="left">不支持</td>
<td align="left">不支持</td>
<td align="left">支持</td>
</tr>
<tr>
<td align="left">R-Tree索引</td>
<td align="left">不支持</td>
<td align="left">支持</td>
<td align="left">不支持</td>
</tr>
<tr>
<td align="left">Full-text</td>
<td align="left">5.6版本后支持</td>
<td align="left">支持</td>
<td align="left">不支持</td>
</tr>
</tbody></table>
<ul>
<li><p>二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢</p>
</li>
<li><p>为了解决上述问题，可以使用 <strong>B-Tree</strong> 结构。<br>B-Tree (多路平衡查找树) 以一棵最大度数（max-degree，指一个节点的子节点个数）为5（5阶）的 b-tree 为例（每个节点最多存储4个key，5个指针）</p>
</li>
<li><p>MySQL 索引数据结构对经典的 B+Tree 进行了优化。在原 B+Tree 的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的 B+Tree，提高区间访问的性能。</p>
</li>
<li><p>哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。<br>如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决。</p>
<ul>
<li>特点：<ul>
<li>Hash索引只能用于对等比较（&#x3D;、in），不支持范围查询（betwwn、&gt;、&lt;、…）</li>
<li>无法利用索引完成排序操作</li>
<li>查询效率高，通常只需要一次检索就可以了，效率通常要高于 B+Tree 索引</li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="5-为什么InnoDB存储引擎使用B-tree索引结构："><a href="#5-为什么InnoDB存储引擎使用B-tree索引结构：" class="headerlink" title="5.为什么InnoDB存储引擎使用B+tree索引结构："></a>5.为什么InnoDB存储引擎使用B+tree索引结构：</h3><ol>
<li>相对于二叉树，层级更少，搜索效率高；</li>
<li>对于B-tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低。</li>
<li>相对Hash索引，B+tree支持范围匹配及排序操作</li>
</ol>
<h3 id="6-索引的分类："><a href="#6-索引的分类：" class="headerlink" title="6.索引的分类："></a>6.索引的分类：</h3><table>
<thead>
<tr>
<th align="left">分类</th>
<th align="left">含义</th>
<th align="left">特点</th>
<th align="left">关键字</th>
</tr>
</thead>
<tbody><tr>
<td align="left">主键索引</td>
<td align="left">针对于表中主键创建的索引</td>
<td align="left">默认自动创建，只能有一个</td>
<td align="left">primary</td>
</tr>
<tr>
<td align="left">唯一索引</td>
<td align="left">避免同一个表中某数据列中的值重复</td>
<td align="left">可以有一 个</td>
<td align="left">unique</td>
</tr>
<tr>
<td align="left">常规索引</td>
<td align="left">快速定位特定数据</td>
<td align="left">可以有多个</td>
<td align="left"></td>
</tr>
<tr>
<td align="left">全文索引</td>
<td align="left">全文索引查找的是文本中的关键词，而不是比较索引中的值</td>
<td align="left">可以有多个</td>
<td align="left">fulltext</td>
</tr>
</tbody></table>
<p>在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：</p>
<table>
<thead>
<tr>
<th align="left">分类</th>
<th align="left">含义</th>
<th align="left">特点</th>
</tr>
</thead>
<tbody><tr>
<td align="left">聚集索引(Clustered Index)</td>
<td align="left">将数据存储与索引放一块，索引结构的叶子节点保存了行数据</td>
<td align="left">必须有，而且只有一个</td>
</tr>
<tr>
<td align="left">二级索引(Secondary Index)</td>
<td align="left">将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键</td>
<td align="left">可以存在多个</td>
</tr>
</tbody></table>
<p>聚集索引选取规则：</p>
<ul>
<li>如果存在主键，主键索引就是聚集索引</li>
<li>如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。</li>
<li>如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引</li>
</ul>
<p>二级索引选取：</p>
<ul>
<li>除去主键，其他字段（name，gender）创建的索引就是二级索引</li>
</ul>
<p>回表查询：</p>
<ul>
<li>如：select * from user where name&#x3D;’ Arm ‘; 会在二级索引中拿到id值，到聚集索引中查询</li>
</ul>
<h3 id="7-多列索引："><a href="#7-多列索引：" class="headerlink" title="7.多列索引："></a>7.多列索引：</h3><ul>
<li>使用多列索引时一定要特别注意，只有使用了索引中的第一个字段时才会触发索引。</li>
<li>如果没有使用索引中的第一个字段，那么这个多列索引就不会起作用。</li>
<li>也就是说多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。</li>
</ul>
<h3 id="8-索引的语法："><a href="#8-索引的语法：" class="headerlink" title="8.索引的语法："></a>8.索引的语法：</h3><ul>
<li><p>创建索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> [<span class="keyword">unique</span> <span class="operator">|</span> funlltext <span class="operator">|</span> spatial] index 索引名 <span class="keyword">on</span> 表名(字段名[(length)] [<span class="keyword">asc</span> <span class="operator">|</span> <span class="keyword">desc</span>])      </span><br><span class="line">例：</span><br><span class="line"><span class="comment">-- name字段为姓名字段，该字段的值可能会重复，为该字段创建（常规索引）</span></span><br><span class="line"><span class="keyword">create</span> index idx_user_name <span class="keyword">on</span> tb_user(name);</span><br><span class="line"><span class="comment">-- phone手机号字段的值非空，且唯一，为该字段创建（唯一索引）</span></span><br><span class="line"><span class="keyword">create</span> <span class="keyword">unique</span> index idx_user_phone <span class="keyword">on</span> tb_user (phone);</span><br><span class="line"><span class="comment">-- 为profession, age, status创建（联合索引）</span></span><br><span class="line"><span class="keyword">create</span> index idx_user_pro_age_stat <span class="keyword">on</span> tb_user(profession, age, status);</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">table</span> index1(</span><br><span class="line">    id <span class="type">int</span>,</span><br><span class="line">    Name <span class="type">varchar</span>(<span class="number">20</span>),</span><br><span class="line">    Sex <span class="type">boolean</span>,</span><br><span class="line">    index index4_st(id)</span><br><span class="line">);</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> index <span class="keyword">from</span> 表名</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改索引：</p>
<figure class="highlight plaintext"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">alter table [表名] add index [索引名] on (列名)</span><br></pre></td></tr></table></figure>
</li>
<li><p>删除索引：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> index 索引名 <span class="keyword">on</span> 表名</span><br><span class="line">例：</span><br><span class="line"><span class="keyword">drop</span> index idx_user_email <span class="keyword">on</span> tb_user;</span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="9-MySQL使用索引的场景："><a href="#9-MySQL使用索引的场景：" class="headerlink" title="9.MySQL使用索引的场景："></a>9.MySQL使用索引的场景：</h3><ul>
<li>快速查找符合where条件的记录</li>
<li>快速确定候选集。若where条件使用了多个索引字段，则MySQL会优先使用能使候选记录集规模最小的那个索引，以便尽快淘汰不符合条件的记录。</li>
<li>如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找。</li>
<li>多表做join操作时会使用索引（如果参与join的字段在这些表中均建立了索引的话）。</li>
<li>若某字段已建立索引，求该字段的min()或max()时，MySQL会使用索引</li>
<li>对建立了索引的字段做sort或group操作时，MySQL会使用索引</li>
</ul>
<h3 id="10-MySQL索引的优化："><a href="#10-MySQL索引的优化：" class="headerlink" title="10.MySQL索引的优化："></a>10.MySQL索引的优化：</h3><ul>
<li>针对于数据量较大，且查询比较频繁的表建立索引</li>
<li>针对于常作为查询条件（where）、排序（order by）、分组（group by）操作的字段建立索引</li>
<li>尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高</li>
<li>如果是字符串类型的字段，字段长度较长，可以针对于字段的特点，建立前缀索引</li>
<li>尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率</li>
<li>要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价就越大，会影响增删改的效率</li>
<li>如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询</li>
</ul>
<h2 id="15-MySQL的锁："><a href="#15-MySQL的锁：" class="headerlink" title="15.MySQL的锁："></a>15.MySQL的锁：</h2><h3 id="1-锁的概述："><a href="#1-锁的概述：" class="headerlink" title="1.锁的概述："></a>1.锁的概述：</h3><ul>
<li><p><strong>锁是计算机协调多个进程或线程并访问某一资源的机制</strong>。</p>
</li>
<li><p>在数据库中，除传统的计算机资源（CPU，RAM，I&#x2F;O）的争用以外，数据也是一种供许多用户共享资源。如何保证数据并发访问的一致性，有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个因素。从这个角度来说，锁对数据库而言显得尤为重要，也更为复杂</p>
</li>
<li><p>分类：MySQL中的锁，按照锁的粒度分，分为以下三类</p>
<table>
<thead>
<tr>
<th>MySQL锁的分类</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>全局锁</td>
<td>锁定数据库中的所有表，锁的粒度小</td>
</tr>
<tr>
<td>表级锁</td>
<td>每次操作锁住整张表，锁定粒度大，锁冲突的概念最高，并发度最低</td>
</tr>
<tr>
<td>行级锁</td>
<td>每次操作锁住对应的行，锁定粒度最小，锁冲突的概念最低，并发度最高</td>
</tr>
</tbody></table>
</li>
</ul>
<h3 id="2-全局锁："><a href="#2-全局锁：" class="headerlink" title="2.全局锁："></a>2.全局锁：</h3><ul>
<li><p>全局锁就是对整个<strong>数据库实例加锁</strong>，加锁后整个实例处于<strong>只读状态</strong>，后续的DML的写语句，DDL语句，已经更新操作的事务提交语句都将被堵塞</p>
</li>
<li><p><strong>锁的粒度小</strong></p>
</li>
<li><p>其典型的使用场景：做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性</p>
</li>
<li><p>一致性数据备份：</p>
<ol>
<li><p>给数据库加上全局锁（加锁后所有的更新操作都不能执行）：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">flush tables <span class="keyword">with</span> read lock;</span><br></pre></td></tr></table></figure>
</li>
<li><p>进行数据备份（命令行）：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysqldump <span class="operator">-</span>u用户名 <span class="operator">-</span>p密码 <span class="operator">-</span>h地址 数据库名 <span class="operator">&gt;</span> 路径<span class="operator">+</span>文件名.<span class="keyword">sql</span></span><br><span class="line">例：</span><br><span class="line">mysqldump <span class="operator">-</span>uroot <span class="operator">-</span>p <span class="operator">-</span>h192<span class="number">.168</span><span class="number">.2</span><span class="number">.25</span> springboot <span class="operator">&gt;</span> D:<span class="operator">/</span>user.sql</span><br></pre></td></tr></table></figure>
</li>
<li><p>取消全局锁：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">unlock tables;</span><br></pre></td></tr></table></figure></li>
</ol>
</li>
<li><p>数据库中加全局锁，是一个比较重的操作，存在以下问题</p>
<ul>
<li>如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆</li>
<li>如果在从表上备份，那么可以在备份期间从库不能执行主库同步过来地二进制日志（binlog），会导致主从延迟。</li>
</ul>
</li>
<li><p>在InnoDB引擎中，我们可以在备份时加上参数–single-transaction参数来<strong>完成不加锁</strong>的一致性数据备份（命令行）。</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">mysqldump <span class="comment">--single-transaction -u用户名 -p密码 -h地址 数据库名 &gt; 路径+文件名.sql</span></span><br><span class="line">例：</span><br><span class="line">mysqldump <span class="comment">--single-transaction -uroot -p -h192.168.2.25 springboot &gt; D:/user.sql</span></span><br></pre></td></tr></table></figure></li>
</ul>
<h3 id="3-表级锁："><a href="#3-表级锁：" class="headerlink" title="3.表级锁："></a>3.表级锁：</h3><ul>
<li><p>表级锁，每次操作<strong>锁住整张表</strong>。<strong>锁定粒度大</strong>，发生<strong>锁冲突的概念最高</strong>，<strong>并发度最低</strong>。应用在MyISAM，InnoDB，BDB等存储引擎中。</p>
</li>
<li><p>对于表级锁，主要分为以下三类：</p>
<table>
<thead>
<tr>
<th>表锁的分类</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>表锁</td>
<td>手动控制，分为：表共享读锁和表独占写锁</td>
</tr>
<tr>
<td>元数据锁</td>
<td>自动控制，为了避免DML(增删改)与DDL冲突，保证读写的正确性</td>
</tr>
<tr>
<td>意向锁</td>
<td>为了避免DML在执行时，加行锁与表锁的冲突</td>
</tr>
</tbody></table>
<ul>
<li><p><strong>表锁</strong>：</p>
<ul>
<li><p>表共享读锁（read lock）：会阻塞客户端和其他客户端对表的写操作</p>
<p><img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220530112838772.png" alt="image-20220530112838772"></p>
</li>
<li><p>表独占写锁（write lock）：会阻塞其他客户端对表的读操作和写操作</p>
<p><img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220530112857794.png" alt="image-20220530112857794"></p>
</li>
<li><p>语法：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">加锁：lock tables 表名 read<span class="operator">/</span>write;   <span class="comment">-- read表示加表共享读锁，write表示加表独占写锁</span></span><br><span class="line">例：</span><br><span class="line">lock tables ssm read;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">释放锁：unlock tables   <span class="operator">/</span>  客户端断开;</span><br><span class="line">例:</span><br><span class="line">unlock tables;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p><strong>元数据锁</strong>（meta data lock,MDL）</p>
<ul>
<li><p>MDL加锁过程<strong>是系统自动控制</strong>，无需显式使用，在访问一张表的时候会自动加上。MDL锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。</p>
</li>
<li><p><strong>为了避免DML(增删改)与DDL冲突，保证读写的正确性。</strong></p>
</li>
<li><p>在MySQL5.5中引入了MDL，当对一张表进行增删改查的时候，<strong>加MDL读锁（共享）</strong>；当对表结构进行变更操作的时候，加<strong>MDL写锁（排他）</strong>。</p>
<p><img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220530222134570.png" alt="image-20220530222134570"></p>
</li>
<li><p>查询元数据锁：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> object_type,object_schema,object_name,lock_duration <span class="keyword">from</span> performance_schema.metadata_locks;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p><strong>意向锁</strong></p>
<ul>
<li><p><strong>为了避免DML在执行时，加行锁与表锁的冲突</strong></p>
</li>
<li><p>在InnoDB中引入意向锁，<strong>使得表锁不用检查每行数据是否加锁</strong>，使用意向锁来减少表锁的检查。</p>
</li>
<li><p>意向共享锁（IS）：与表锁共享锁(read)兼容，与表锁排它锁(write)互斥</p>
</li>
<li><p>意向排他锁（IX）：与表锁共享锁（read）及排它锁（write）都互斥。意向锁之间不会互斥</p>
</li>
<li><p>添加意向锁：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">意向共享锁(<span class="keyword">IS</span>)：查询语句 lock <span class="keyword">in</span> share mode;</span><br></pre></td></tr></table></figure>

<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">意向排他锁(IX)：增删改查语句 <span class="keyword">for</span> <span class="keyword">update</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>查看意向锁及行锁的加锁情况：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> object_schema,object_name,index_name,lock_type,lock_mode,lock_data <span class="keyword">from</span> performance_schema.data_locks;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
</ul>
</li>
</ul>
<h3 id="4-行级锁："><a href="#4-行级锁：" class="headerlink" title="4.行级锁："></a>4.行级锁：</h3><ul>
<li><p>行级锁，每次操作锁住对应的行数组。<strong>锁定粒度最小</strong>，发生<strong>锁冲突的概念最低</strong>，<strong>并发度最高</strong>，应用在InnoDB存储引擎中。</p>
</li>
<li><p>InnoDB的数据时基于索引组织的，行锁是通过<strong>对索引上的索引项来实现</strong>，而不是对记录加的锁。</p>
</li>
<li><p>对于行级锁，主要分为以下三类：</p>
<ul>
<li><p>行锁(Reord Lock)：锁定单个记录的锁，防止其他事务对此行进行update和delete。在RC，RR隔离级别下都支持。</p>
<img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220716192823374.png" alt="image-20220716192823374" style="zoom:80%;" />
</li>
<li><p>间隔锁(Gap Lock)：锁定索引记录间隔(不含该记录)，确定索引间隔不变，防止其他事务在这个间隔进行insert，产生幻读。在RR隔离级别都支持。</p>
<img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220716192748422.png" alt="image-20220716192748422" style="zoom:80%;" />
</li>
<li><p>临时锁(Next-Key Lock)：行锁和间隔组合，同时锁住数据，并锁住数据前面的间隔Gap，在RR隔离级别下支持。</p>
<img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220716193013697.png" alt="image-20220716193013697" style="zoom:80%;" /></li>
</ul>
</li>
<li><p><strong>行锁</strong>：InnoDB实现了以下两种类型的行锁：</p>
<ul>
<li>共享锁(S)：允许一个事务去读一行，阻止其他事务获取的相同数据集的排它锁。</li>
<li>排它锁(X)：允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁个排他锁。</li>
</ul>
<p><img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220530235220431.png" alt="image-20220530235220431"></p>
<ul>
<li><p>不同语句加锁的类型：</p>
<p><img src="C:\Users\user\AppData\Roaming\Typora\typora-user-images\image-20220716193355809.png" alt="image-20220716193355809"></p>
</li>
<li><p>默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读</p>
<ol>
<li>针对唯一索引进行检索时，对已存在的记录进行等值匹配时，将会自动优化为行锁</li>
<li>InnoDB的行锁是针对于索引的锁，<strong>不通过索引条件检索数据</strong>，那么InnoDB将对表中的所有记录加锁，此时就会<strong>升级为表锁</strong>。</li>
</ol>
</li>
<li><p>可以通过以下SQL，查看意向锁及行锁的加锁情况：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> object_schema,object_name,index_name,lock_type,lock_mode,lock_data <span class="keyword">from</span> performance_schema.data_locks;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>间隔锁&#x2F;临键锁：</p>
<ul>
<li>默认情况下，InnoDB在repeatable read事务隔离级别运行，InnoDB使用next-key锁进行搜索和索引扫描，以防止幻读<ol>
<li>索引上的等值查询（唯一索引），给不存在的记录加锁时，优化为间隔锁</li>
<li>索引的等值查询（普通索引），向右遍历时最后一个值不满足查询需求时，next-key lock退化为间隔锁</li>
<li>索引上范围查询（唯一索引），会访问到不满足条件的第一只为止</li>
</ol>
</li>
<li>注意：<strong>间隔锁唯一的目的是防止其他事务插入间隔</strong>。间隔锁可以共存，一个事务采用的间隔锁不会阻止另一个事务在同已间隔上采用间隔锁。</li>
</ul>
</li>
</ul>
<h2 id="16-MySQL事务："><a href="#16-MySQL事务：" class="headerlink" title="16.MySQL事务："></a>16.MySQL事务：</h2><h3 id="1-事务的基本介绍"><a href="#1-事务的基本介绍" class="headerlink" title="1.事务的基本介绍:"></a>1.事务的基本介绍:</h3><ul>
<li><p>概念：如果一个包含多个步骤的业务操作，被事务管理，那么<strong>这些操作要么同时成功，要么同时失败</strong></p>
</li>
<li><p>如果开启事务，而未提交事务；看到的数据是临时的并没有生效</p>
<table>
<thead>
<tr>
<th>事务提交的两种形式：</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>自动提交</td>
<td>MySQL就是自动提交的，一条DML(增删改)语句会自动提交一次事务</td>
</tr>
<tr>
<td>手动提交</td>
<td>Oracle数据库默认是手动提交事务，需要先开启事务，在提交</td>
</tr>
</tbody></table>
</li>
<li><p><strong>修改事务默认提交方式：</strong></p>
<ul>
<li><p>查找事务的默认提交方式：1：代表自动提交 0：代表手动提交</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> @<span class="variable">@autocommit</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>修改默认提交方式：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">set</span> @<span class="variable">@autocommit</span><span class="operator">=</span><span class="number">1</span>;</span><br></pre></td></tr></table></figure></li>
</ul>
</li>
<li><p>操作：</p>
<ol>
<li><p>开启事务：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">[<span class="keyword">start</span> transaction<span class="operator">/</span><span class="keyword">begin</span>];</span><br></pre></td></tr></table></figure>
</li>
<li><p>回滚：—发现出问题了，回滚事务</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">rollback</span>;</span><br></pre></td></tr></table></figure>
</li>
<li><p>提交:  —发现执行没有问题，提交事务</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">commit</span>;</span><br></pre></td></tr></table></figure>

<p>——开启事务—-（多个操作）—-提交事务—-回滚事务</p>
</li>
</ol>
</li>
</ul>
<h3 id="2-事务的四大特征"><a href="#2-事务的四大特征" class="headerlink" title="2.事务的四大特征:"></a>2.事务的四大特征:</h3><table>
<thead>
<tr>
<th>四大特征</th>
<th>说明</th>
<th>保证方式</th>
</tr>
</thead>
<tbody><tr>
<td><strong>原子性</strong></td>
<td>Atomicity，是不可分割的最小操作单位，要么同时成功，要么同时失败</td>
<td>undo log日志</td>
</tr>
<tr>
<td><strong>持久性</strong></td>
<td>Consistency，当事务提交或回滚后，数据库会持久化的保存数据</td>
<td>redo log日志</td>
</tr>
<tr>
<td><strong>隔离性</strong></td>
<td>Isolution，多个事务之间，相互独立</td>
<td>锁机制和MVCC</td>
</tr>
<tr>
<td><strong>一致性</strong></td>
<td>Durability，事务操作前后，数据库的完整性约束没有被破坏，都是合法的数据状态。</td>
<td>前三种</td>
</tr>
</tbody></table>
<img src="C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20230214122639402.png" alt="image-20230214122639402" style="zoom:67%;" />

<h3 id="3-InnoDB事务的ACID如何保证："><a href="#3-InnoDB事务的ACID如何保证：" class="headerlink" title="3.InnoDB事务的ACID如何保证："></a>3.InnoDB事务的ACID如何保证：</h3><ul>
<li>InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)</li>
<li>redo log(重做日志)：<ul>
<li>作用：记录的是对数据的操作，用于保证事务持久性</li>
<li>为什么出现：<ul>
<li>InnoDB提供了缓存（Buffer Pool）来提高数据读写效率；如：需要读取数据时，会先从缓存读取，再从磁盘中读取；需要写入数据时，先写入缓存，再定期刷入磁盘</li>
<li>但如果MySQL宕机，但缓存修改的数据没有刷入磁盘就会导致数据的丢失，事务的持久性就无法保证；所以出现了redo log日志</li>
</ul>
</li>
<li>如何解决<strong>持久性</strong>问题：<ul>
<li>当数据修改时，除了修改缓存中的数据，（先）还会在redolog记录这次操作</li>
<li>当事务成功提交后时，会对redo log进行刷新</li>
<li>如果MySQL宕机，重启后可以读取redo log的数据进行恢复</li>
</ul>
</li>
</ul>
</li>
<li>undo log(回滚日志)：<ul>
<li>作用：记录的是sql执行相关的信息，是事务原子性和隔离性实现的基础</li>
<li>如何解决<strong>原子性</strong>问题：<ul>
<li>当事务对数据库进行修改时，InnoDB会生成对应的undo log；</li>
<li>当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作；如对于每个insert，回滚时会执行delete；对于每个update，会记录修改的行，修改前后的值，回滚时会update回去。</li>
</ul>
</li>
<li>如何解决<strong>隔离性</strong>问题：<ul>
<li>锁机制保证隔离性：(一个事务)写操作对(另一个事务)写操作的影响</li>
<li>MVCC保证隔离性：(一个事务)写操作对(另一个事务)读操作的影响</li>
</ul>
</li>
</ul>
</li>
<li>一致性（Durability）：<ul>
<li>一致性是事务追求的最终目标</li>
<li>通过保证事务的原子性，持久性，隔离性来保证事务的一致性。</li>
</ul>
</li>
</ul>
<h3 id="4-InnoDB引擎的MVCC："><a href="#4-InnoDB引擎的MVCC：" class="headerlink" title="4.InnoDB引擎的MVCC："></a>4.InnoDB引擎的MVCC：</h3><ul>
<li>概述：<ul>
<li>全称Multi-version Concurrency Controller（多版本并发控制），指维护一个数据的多个版本，使得读写操作没有冲突</li>
<li>是一种用来解决读-写冲突的无锁并发控制机制</li>
<li>在并发读写数据库时,可以做到在读操作时不用阻塞写操作,写操作也不用阻塞读操作</li>
</ul>
</li>
<li>作用：<ul>
<li>提高数据库并发性能</li>
<li>解决脏读、幻读、不可重复读等事务隔离问题，但不能解决更新丢失问题</li>
</ul>
</li>
</ul>
<h3 id="5-并发情况下，读操作可能存在的三类问题："><a href="#5-并发情况下，读操作可能存在的三类问题：" class="headerlink" title="5.并发情况下，读操作可能存在的三类问题："></a>5.并发情况下，读操作可能存在的三类问题：</h3><ul>
<li>脏读：<ul>
<li>概述：事务 A 读取事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据。（侧重于新增或删除）</li>
<li>解决方法：加表锁</li>
</ul>
</li>
<li>不可重复读（虚读）：<ul>
<li>概述：在同一事务中，事务 A 多次读取数据，事务B在事务A读取过程中对数据更新且提交，导致事务A前后读取到的数据不一样（侧重于修改）</li>
<li>解决方法：加行锁</li>
</ul>
</li>
<li>幻读：<ul>
<li>概述：事务A操作（DML）数据表中的所有记录，事务B添加了一条数据，则事务A查询不到自己的修改</li>
<li>幻读产生的原因：行锁只能锁住行，即使把所有的行记录都上锁，也阻止不了新插入的记录</li>
<li>解决方法：要通过next-key lock。next-key lock是行锁的一种，实现相当于record lock(记录锁)+ gap lock(间隙锁)；</li>
</ul>
</li>
</ul>
<h3 id="6-事务的隔离级别"><a href="#6-事务的隔离级别" class="headerlink" title="6.事务的隔离级别:"></a>6.事务的隔离级别:</h3><ul>
<li><p>概念：</p>
<ul>
<li>多个事务之间隔离的，相互独立的，但是<strong>如果多个事务操作同一批数据，则会引发一些问题</strong>，设置不同的隔离级别就可以解决这些问题</li>
<li>隔离级别越低，系统开销越低，可支持的并发越高，但隔离性也越差</li>
</ul>
</li>
<li><p><strong>隔离级别:</strong></p>
<table>
<thead>
<tr>
<th>隔离级别</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>read uncommitted</td>
<td>读未提交，产生问题：脏读，不可重复读，幻读</td>
</tr>
<tr>
<td>read committed</td>
<td>读已提交，产生问题：不可重复读，幻读</td>
</tr>
<tr>
<td>repeatable read</td>
<td>可重复读，产生的问题:幻读（默认隔离级别）</td>
</tr>
<tr>
<td>serializable</td>
<td>串行化，可以解决所有的问题</td>
</tr>
</tbody></table>
<ul>
<li>注意:隔离级别从小到大安全性越来越大，但是效率越来越低</li>
</ul>
</li>
<li><p>数据库查询隔离级别</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> @<span class="variable">@tx</span>_isolation;</span><br></pre></td></tr></table></figure>
</li>
<li><p>数据库设置隔离级别:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="comment">-- set global transaction isolation level 级别字符串</span></span><br><span class="line"><span class="keyword">set</span> <span class="keyword">global</span> transaction isolation level serializable</span><br></pre></td></tr></table></figure></li>
</ul>
<h2 id="12-数据库设计的范式："><a href="#12-数据库设计的范式：" class="headerlink" title="12.数据库设计的范式："></a>12.数据库设计的范式：</h2><h3 id="1-概述："><a href="#1-概述：" class="headerlink" title="1.概述："></a>1.概述：</h3><ul>
<li><p>概念：数据库范式是数据库的设计规范，遵守不同的规范要求，可以设计出合理的关系型数据库；范式越高，数据冗余越小</p>
</li>
<li><p>作用：使用数据结构更加合理，使用冗余（重复数据）尽量小，便于增删改</p>
</li>
</ul>
<h3 id="2-依赖："><a href="#2-依赖：" class="headerlink" title="2.依赖："></a>2.依赖：</h3><ul>
<li><p>函数依赖：通过唯一的X一定能查找到Y，即为”Y依赖于X”（X —&gt; Y）</p>
<p><img src="C:\Users\Administrator\AppData\Roaming\Typora\typora-user-images\image-20230309234404784.png" alt="image-20230309234404784"></p>
</li>
</ul>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta">Author: </span><span class="post-copyright-info"><a href="http://example.com">阿尔托莉雅</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta">Link: </span><span class="post-copyright-info"><a href="http://example.com/2023/03/13/4.%E6%95%B0%E6%8D%AE%E5%BA%93/A1.MySQL-%E6%AC%A7%E9%B9%8F%E7%AF%87/">http://example.com/2023/03/13/4.%E6%95%B0%E6%8D%AE%E5%BA%93/A1.MySQL-%E6%AC%A7%E9%B9%8F%E7%AF%87/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta">Copyright Notice: </span><span class="post-copyright-info">All articles in this blog are licensed under <a target="_blank" rel="noopener" href="https://creativecommons.org/licenses/by-nc-sa/4.0/">CC BY-NC-SA 4.0</a> unless stating additionally.</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/mySQL-SQL-%E6%95%B0%E6%8D%AE%E5%BA%93/">mySQL SQL 数据库</a></div><div class="post_share"><div class="social-share" data-image="/images/Otherwallpaper/avatar.png" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><div class="prev-post pull-left"><a href="/2023/03/13/4.%E6%95%B0%E6%8D%AE%E5%BA%93/B2.NoSQL%E6%95%B0%E6%8D%AE%E5%BA%93%20-%20Redis/" title="NoSQL篇-Redis数据库"><div class="cover" style="background: var(--default-bg-color)"></div><div class="pagination-info"><div class="label">Previous Post</div><div class="prev_info">NoSQL篇-Redis数据库</div></div></a></div><div class="next-post pull-right"><a href="/2023/03/13/3.linux/D2.Ansible%E5%89%A7%E6%9C%AC/" title="Ansible"><div class="cover" style="background: var(--default-bg-color)"></div><div class="pagination-info"><div class="label">Next Post</div><div class="next_info">Ansible</div></div></a></div></nav></div><div class="aside-content" id="aside-content"><div class="card-widget card-info"><div class="is-center"><div class="avatar-img"><img src="/images/Otherwallpaper/avatar.png" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/></div><div class="author-info__name">阿尔托莉雅</div><div class="author-info__description"></div></div><div class="card-info-data site-data is-center"><a href="/archives/"><div class="headline">Articles</div><div class="length-num">11</div></a><a href="/tags/"><div class="headline">Tags</div><div class="length-num">4</div></a><a href="/categories/"><div class="headline">Categories</div><div class="length-num">2</div></a></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xxxxxx"><i class="fab fa-github"></i><span>Follow Me</span></a><div class="card-info-social-icons is-center"><a class="social-icon" href="https://toscode.gitee.com/blue-fantasy" target="_blank" title="Gitee"><i class="fab fa-github"></i></a><a class="social-icon" href="/1095322098@qq.com" target="_blank" title="Email"><i class="fas fa-envelope"></i></a></div></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>Announcement</span></div><div class="announcement_content">This is my Blog</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>Catalog</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-2"><a class="toc-link" href="#1-%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%A6%82%E5%BF%B5%EF%BC%9A"><span class="toc-number">1.</span> <span class="toc-text">1.数据库的基本概念：</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#2-MySQL%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%AE%89%E8%A3%85%E9%85%8D%E7%BD%AE%EF%BC%9A"><span class="toc-number">2.</span> <span class="toc-text">2.MySQL数据库的安装配置：</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#3-MySQL%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%9C%8D%E5%8A%A1%E6%93%8D%E4%BD%9C%EF%BC%9A"><span class="toc-number">3.</span> <span class="toc-text">3.MySQL数据库的服务操作：</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#4-%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E6%95%B0%E6%8D%AE%E7%B1%BB%E5%9E%8B"><span class="toc-number">4.</span> <span class="toc-text">4.数据库的数据类型:</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#5-SQL%E8%AF%AD%E8%A8%80%EF%BC%9A"><span class="toc-number">5.</span> <span class="toc-text">5.SQL语言：</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#6-%E6%93%8D%E4%BD%9C%E6%95%B0%E6%8D%AE%E5%BA%93%EF%BC%9ACRUD"><span class="toc-number">6.</span> <span class="toc-text">6.操作数据库：CRUD:</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-C-Create-%E5%88%9B%E5%BB%BA"><span class="toc-number">6.1.</span> <span class="toc-text">1.C(Create):创建:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-R-show-%E6%9F%A5%E8%AF%A2"><span class="toc-number">6.2.</span> <span class="toc-text">2.R(show):查询:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-U-alter-%E4%BF%AE%E6%94%B9"><span class="toc-number">6.3.</span> <span class="toc-text">3.U(alter):修改:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-D%EF%BC%88drop%EF%BC%89-%E5%88%A0%E9%99%A4"><span class="toc-number">6.4.</span> <span class="toc-text">4.D（drop）:删除</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E4%BD%BF%E7%94%A8%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">6.5.</span> <span class="toc-text">5.使用数据库:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-%E8%AE%BE%E7%BD%AE%E6%8F%90%E7%A4%BA%E7%AC%A6%E6%98%BE%E7%A4%BA%E5%BD%93%E5%89%8D%E6%95%B0%E6%8D%AE%E5%BA%93"><span class="toc-number">6.6.</span> <span class="toc-text">6.设置提示符显示当前数据库:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%AD%97%E7%AC%A6%E9%9B%86%EF%BC%9A"><span class="toc-number">6.7.</span> <span class="toc-text">7.数据库的字符集：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#8-MySQL%E7%B3%BB%E7%BB%9F%E6%95%B0%E6%8D%AE%E5%BA%93%EF%BC%9A"><span class="toc-number">6.8.</span> <span class="toc-text">8.MySQL系统数据库：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#7-%E8%A1%A8%E7%9A%84%E5%9F%BA%E6%9C%AC%E6%93%8D%E4%BD%9C"><span class="toc-number">7.</span> <span class="toc-text">7.表的基本操作:</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-C-Create-%E5%88%9B%E5%BB%BA-1"><span class="toc-number">7.1.</span> <span class="toc-text">1.C(Create):创建</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-R-show-%E6%9F%A5%E8%AF%A2-1"><span class="toc-number">7.2.</span> <span class="toc-text">2.R(show):查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-U-alter-%E4%BF%AE%E6%94%B9-1"><span class="toc-number">7.3.</span> <span class="toc-text">3.U(alter):修改</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-D%EF%BC%88drop%EF%BC%89-%E5%88%A0%E9%99%A4-1"><span class="toc-number">7.4.</span> <span class="toc-text">4.D（drop）:删除</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E8%A1%A8%E7%9A%84%E7%BA%A6%E6%9D%9F%EF%BC%9A"><span class="toc-number">7.5.</span> <span class="toc-text">5.表的约束：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#8-MySQL%E7%9A%84%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%EF%BC%9A"><span class="toc-number">8.</span> <span class="toc-text">8.MySQL的存储引擎：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E7%9A%84%E6%A6%82%E5%BF%B5%EF%BC%9A"><span class="toc-number">8.1.</span> <span class="toc-text">1.存储引擎的概念：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-InnoDB%EF%BC%8CMyISAM%EF%BC%8CMemory%E7%9A%84%E5%8C%BA%E5%88%AB%EF%BC%9A"><span class="toc-number">8.2.</span> <span class="toc-text">2.InnoDB，MyISAM，Memory的区别：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E7%9A%84%E6%93%8D%E4%BD%9C%EF%BC%9A"><span class="toc-number">8.3.</span> <span class="toc-text">4.存储引擎的操作：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#8-DCL-%E7%AE%A1%E7%90%86%E7%94%A8%E6%88%B7%E5%92%8C%E6%8E%88%E6%9D%83-%EF%BC%9A"><span class="toc-number">9.</span> <span class="toc-text">8.DCL(管理用户和授权)：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E7%AE%A1%E7%90%86%E7%94%A8%E6%88%B7%EF%BC%9A"><span class="toc-number">9.1.</span> <span class="toc-text">1.管理用户：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E6%8E%88%E6%9D%83%EF%BC%9A"><span class="toc-number">9.2.</span> <span class="toc-text">2.授权：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E5%AF%86%E7%A0%81%E7%AD%96%E7%95%A5%EF%BC%9A"><span class="toc-number">9.3.</span> <span class="toc-text">3.密码策略：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#9-MySQL%E7%9A%84DML%E8%AF%AD%E5%8F%A5"><span class="toc-number">10.</span> <span class="toc-text">9.MySQL的DML语句</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-insert-x2F-replace%E8%AF%AD%E5%8F%A5%EF%BC%9A%E6%B7%BB%E5%8A%A0%E6%95%B0%E6%8D%AE"><span class="toc-number">10.1.</span> <span class="toc-text">1. insert&#x2F; replace语句：添加数据</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-delete-x2F-truncate-x2F-drop%E8%AF%AD%E5%8F%A5%EF%BC%9A%E5%88%A0%E9%99%A4%E6%95%B0%E6%8D%AE%EF%BC%9A"><span class="toc-number">10.2.</span> <span class="toc-text">2.delete &#x2F; truncate&#x2F;drop语句：删除数据：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-update%E8%AF%AD%E5%8F%A5%EF%BC%9A%E4%BF%AE%E6%94%B9%E6%95%B0%E6%8D%AE%EF%BC%9A"><span class="toc-number">10.3.</span> <span class="toc-text">3.update语句：修改数据：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-select%E8%AF%AD%E5%8F%A5%EF%BC%9A%E6%9F%A5%E8%AF%A2%E6%95%B0%E6%8D%AE"><span class="toc-number">10.4.</span> <span class="toc-text">4.select语句：查询数据</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#1-%E5%9F%BA%E7%A1%80%E6%9F%A5%E8%AF%A2"><span class="toc-number">10.4.1.</span> <span class="toc-text">1.基础查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#2-%E6%9D%A1%E4%BB%B6%E6%9F%A5%E8%AF%A2"><span class="toc-number">10.4.2.</span> <span class="toc-text">2.条件查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#3-%E6%8E%92%E5%BA%8F%E6%9F%A5%E8%AF%A2"><span class="toc-number">10.4.3.</span> <span class="toc-text">3.排序查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#4-%E5%88%86%E7%BB%84%E6%9F%A5%E8%AF%A2"><span class="toc-number">10.4.4.</span> <span class="toc-text">4.分组查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#5-%E5%88%86%E9%A1%B5%E6%9F%A5%E8%AF%A2"><span class="toc-number">10.4.5.</span> <span class="toc-text">5.分页查询</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#6-%E5%A4%9A%E8%A1%A8%E6%9F%A5%E8%AF%A2"><span class="toc-number">10.4.6.</span> <span class="toc-text">6.多表查询:</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#7-%E5%B5%8C%E5%A5%97%E6%9F%A5%E8%AF%A2%EF%BC%9A"><span class="toc-number">10.4.7.</span> <span class="toc-text">7.嵌套查询：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#8-%E9%9B%86%E5%90%88%E6%9F%A5%E8%AF%A2%EF%BC%9A"><span class="toc-number">10.4.8.</span> <span class="toc-text">8.集合查询：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#9-%E7%AE%97%E6%9C%AF%E8%A1%A8%E8%BE%BE%E5%BC%8F%EF%BC%9A"><span class="toc-number">10.4.9.</span> <span class="toc-text">9.算术表达式：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#10-DQL%E7%BC%96%E5%86%99%E9%A1%BA%E5%BA%8F%EF%BC%9A"><span class="toc-number">10.4.10.</span> <span class="toc-text">10.DQL编写顺序：</span></a></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#10-SQL%E5%87%BD%E6%95%B0%EF%BC%9A"><span class="toc-number">11.</span> <span class="toc-text">10.SQL函数：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E8%81%9A%E5%90%88%E5%87%BD%E6%95%B0"><span class="toc-number">11.1.</span> <span class="toc-text">1.聚合函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%AD%97%E7%AC%A6%E4%B8%B2%E5%87%BD%E6%95%B0%EF%BC%9A"><span class="toc-number">11.2.</span> <span class="toc-text">2.字符串函数：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E6%95%B0%E5%80%BC%E5%87%BD%E6%95%B0%EF%BC%9A"><span class="toc-number">11.3.</span> <span class="toc-text">3.数值函数：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E6%97%A5%E6%9C%9F%E5%87%BD%E6%95%B0%EF%BC%9A"><span class="toc-number">11.4.</span> <span class="toc-text">4.日期函数：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E6%B5%81%E7%A8%8B%E5%87%BD%E6%95%B0%EF%BC%9A"><span class="toc-number">11.5.</span> <span class="toc-text">5.流程函数：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#11-%E7%BA%A7%E8%81%94%E6%93%8D%E4%BD%9C"><span class="toc-number">12.</span> <span class="toc-text">11.级联操作</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%A4%87%E4%BB%BD%E5%92%8C%E8%BF%98%E5%8E%9F%EF%BC%9A"><span class="toc-number">13.</span> <span class="toc-text">12.数据库的备份和还原：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-MySQL%E6%97%A5%E5%BF%97%E7%AE%A1%E7%90%86%EF%BC%9A"><span class="toc-number">13.1.</span> <span class="toc-text">1.MySQL日志管理：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-MySQL%E6%97%A5%E5%BF%97%E7%B1%BB%E5%9E%8B%EF%BC%9A"><span class="toc-number">13.2.</span> <span class="toc-text">2.MySQL日志类型：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E9%94%99%E8%AF%AF%E6%97%A5%E5%BF%97%EF%BC%9A"><span class="toc-number">13.3.</span> <span class="toc-text">3.错误日志：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E9%80%9A%E7%94%A8%E6%9F%A5%E8%AF%A2%E6%97%A5%E5%BF%97%EF%BC%9A"><span class="toc-number">13.4.</span> <span class="toc-text">4.通用查询日志：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E6%85%A2%E6%9F%A5%E8%AF%A2%E6%97%A5%E5%BF%97%EF%BC%9A"><span class="toc-number">13.5.</span> <span class="toc-text">5.慢查询日志：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-%E4%BA%8C%E8%BF%9B%E5%88%B6%E6%97%A5%E5%BF%97%EF%BC%9A"><span class="toc-number">13.6.</span> <span class="toc-text">6.二进制日志：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-MySQL%E7%9A%84%E5%A4%87%E4%BB%BD%EF%BC%9A"><span class="toc-number">13.7.</span> <span class="toc-text">7.MySQL的备份：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#13-MySQL%E8%A7%86%E5%9B%BE%EF%BC%9A"><span class="toc-number">14.</span> <span class="toc-text">13.MySQL视图：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E8%A7%86%E5%9B%BE%E7%9A%84%E6%A6%82%E5%BF%B5%EF%BC%9A"><span class="toc-number">14.1.</span> <span class="toc-text">1.视图的概念：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%BB%BA%E7%AB%8B%E8%A7%86%E5%9B%BE%EF%BC%9A"><span class="toc-number">14.2.</span> <span class="toc-text">2.建立视图：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E6%9B%B4%E6%96%B0%E8%A7%86%E5%9B%BE%EF%BC%9A"><span class="toc-number">14.3.</span> <span class="toc-text">2.更新视图：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E5%88%A0%E9%99%A4%E8%A7%86%E5%9B%BE%EF%BC%9A"><span class="toc-number">14.4.</span> <span class="toc-text">4.删除视图：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E6%9F%A5%E8%AF%A2%E8%A7%86%E5%9B%BE%EF%BC%9A"><span class="toc-number">14.5.</span> <span class="toc-text">5.查询视图：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-%E6%9B%B4%E6%96%B0%E6%95%B0%E6%8D%AE%EF%BC%9A"><span class="toc-number">14.6.</span> <span class="toc-text">6.更新数据：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-%E8%A7%86%E5%9B%BE%E7%9A%84%E4%BD%9C%E7%94%A8%EF%BC%9A"><span class="toc-number">14.7.</span> <span class="toc-text">7.视图的作用：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#14-MySQL%E7%B4%A2%E5%BC%95%EF%BC%9A"><span class="toc-number">15.</span> <span class="toc-text">14.MySQL索引：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E7%B4%A2%E5%BC%95%E7%9A%84%E6%A6%82%E8%BF%B0%EF%BC%9A"><span class="toc-number">15.1.</span> <span class="toc-text">1.索引的概述：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-B%E6%A0%91%EF%BC%9A"><span class="toc-number">15.2.</span> <span class="toc-text">2.B树：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-B-%E6%A0%91%EF%BC%9A"><span class="toc-number">15.3.</span> <span class="toc-text">3.B+树：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E7%B4%A2%E5%BC%95%E7%9A%84%E7%BB%93%E6%9E%84%EF%BC%9A"><span class="toc-number">15.4.</span> <span class="toc-text">4.索引的结构：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E4%B8%BA%E4%BB%80%E4%B9%88InnoDB%E5%AD%98%E5%82%A8%E5%BC%95%E6%93%8E%E4%BD%BF%E7%94%A8B-tree%E7%B4%A2%E5%BC%95%E7%BB%93%E6%9E%84%EF%BC%9A"><span class="toc-number">15.5.</span> <span class="toc-text">5.为什么InnoDB存储引擎使用B+tree索引结构：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-%E7%B4%A2%E5%BC%95%E7%9A%84%E5%88%86%E7%B1%BB%EF%BC%9A"><span class="toc-number">15.6.</span> <span class="toc-text">6.索引的分类：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-%E5%A4%9A%E5%88%97%E7%B4%A2%E5%BC%95%EF%BC%9A"><span class="toc-number">15.7.</span> <span class="toc-text">7.多列索引：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#8-%E7%B4%A2%E5%BC%95%E7%9A%84%E8%AF%AD%E6%B3%95%EF%BC%9A"><span class="toc-number">15.8.</span> <span class="toc-text">8.索引的语法：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#9-MySQL%E4%BD%BF%E7%94%A8%E7%B4%A2%E5%BC%95%E7%9A%84%E5%9C%BA%E6%99%AF%EF%BC%9A"><span class="toc-number">15.9.</span> <span class="toc-text">9.MySQL使用索引的场景：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#10-MySQL%E7%B4%A2%E5%BC%95%E7%9A%84%E4%BC%98%E5%8C%96%EF%BC%9A"><span class="toc-number">15.10.</span> <span class="toc-text">10.MySQL索引的优化：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#15-MySQL%E7%9A%84%E9%94%81%EF%BC%9A"><span class="toc-number">16.</span> <span class="toc-text">15.MySQL的锁：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E9%94%81%E7%9A%84%E6%A6%82%E8%BF%B0%EF%BC%9A"><span class="toc-number">16.1.</span> <span class="toc-text">1.锁的概述：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E5%85%A8%E5%B1%80%E9%94%81%EF%BC%9A"><span class="toc-number">16.2.</span> <span class="toc-text">2.全局锁：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E8%A1%A8%E7%BA%A7%E9%94%81%EF%BC%9A"><span class="toc-number">16.3.</span> <span class="toc-text">3.表级锁：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E8%A1%8C%E7%BA%A7%E9%94%81%EF%BC%9A"><span class="toc-number">16.4.</span> <span class="toc-text">4.行级锁：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#16-MySQL%E4%BA%8B%E5%8A%A1%EF%BC%9A"><span class="toc-number">17.</span> <span class="toc-text">16.MySQL事务：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E4%BA%8B%E5%8A%A1%E7%9A%84%E5%9F%BA%E6%9C%AC%E4%BB%8B%E7%BB%8D"><span class="toc-number">17.1.</span> <span class="toc-text">1.事务的基本介绍:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E4%BA%8B%E5%8A%A1%E7%9A%84%E5%9B%9B%E5%A4%A7%E7%89%B9%E5%BE%81"><span class="toc-number">17.2.</span> <span class="toc-text">2.事务的四大特征:</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-InnoDB%E4%BA%8B%E5%8A%A1%E7%9A%84ACID%E5%A6%82%E4%BD%95%E4%BF%9D%E8%AF%81%EF%BC%9A"><span class="toc-number">17.3.</span> <span class="toc-text">3.InnoDB事务的ACID如何保证：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-InnoDB%E5%BC%95%E6%93%8E%E7%9A%84MVCC%EF%BC%9A"><span class="toc-number">17.4.</span> <span class="toc-text">4.InnoDB引擎的MVCC：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E5%B9%B6%E5%8F%91%E6%83%85%E5%86%B5%E4%B8%8B%EF%BC%8C%E8%AF%BB%E6%93%8D%E4%BD%9C%E5%8F%AF%E8%83%BD%E5%AD%98%E5%9C%A8%E7%9A%84%E4%B8%89%E7%B1%BB%E9%97%AE%E9%A2%98%EF%BC%9A"><span class="toc-number">17.5.</span> <span class="toc-text">5.并发情况下，读操作可能存在的三类问题：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-%E4%BA%8B%E5%8A%A1%E7%9A%84%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB"><span class="toc-number">17.6.</span> <span class="toc-text">6.事务的隔离级别:</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#12-%E6%95%B0%E6%8D%AE%E5%BA%93%E8%AE%BE%E8%AE%A1%E7%9A%84%E8%8C%83%E5%BC%8F%EF%BC%9A"><span class="toc-number">18.</span> <span class="toc-text">12.数据库设计的范式：</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%A6%82%E8%BF%B0%EF%BC%9A"><span class="toc-number">18.1.</span> <span class="toc-text">1.概述：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E4%BE%9D%E8%B5%96%EF%BC%9A"><span class="toc-number">18.2.</span> <span class="toc-text">2.依赖：</span></a></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>Recent Post</span></div><div class="aside-list"><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2023/03/13/4.%E6%95%B0%E6%8D%AE%E5%BA%93/B3.NoSQL%E6%95%B0%E6%8D%AE%E5%BA%93%20-%20MongDB/" title="NoSQL篇-MongDB数据库">NoSQL篇-MongDB数据库</a><time datetime="2023-03-13T12:32:38.445Z" title="Created 2023-03-13 20:32:38">2023-03-13</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2023/03/13/4.%E6%95%B0%E6%8D%AE%E5%BA%93/B2.NoSQL%E6%95%B0%E6%8D%AE%E5%BA%93%20-%20Redis/" title="NoSQL篇-Redis数据库">NoSQL篇-Redis数据库</a><time datetime="2023-03-13T12:32:38.441Z" title="Created 2023-03-13 20:32:38">2023-03-13</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2023/03/13/4.%E6%95%B0%E6%8D%AE%E5%BA%93/A1.MySQL-%E6%AC%A7%E9%B9%8F%E7%AF%87/" title="SQL篇-MySQL数据库">SQL篇-MySQL数据库</a><time datetime="2023-03-13T12:32:38.437Z" title="Created 2023-03-13 20:32:38">2023-03-13</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2023/03/13/3.linux/D2.Ansible%E5%89%A7%E6%9C%AC/" title="Ansible">Ansible</a><time datetime="2023-03-13T12:32:23.866Z" title="Created 2023-03-13 20:32:23">2023-03-13</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2023/03/13/3.linux/D1.Ansible%E8%87%AA%E5%8A%A8%E5%8C%96%E8%BF%90%E7%BB%B4%E5%B7%A5%E5%85%B7/" title="Ansible基础">Ansible基础</a><time datetime="2023-03-13T12:32:23.861Z" title="Created 2023-03-13 20:32:23">2023-03-13</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2020 - 2023 By 阿尔托莉雅</div><div class="framework-info"><span>Framework </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo</a><span class="footer-separator">|</span><span>Theme </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="Read Mode"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="Toggle Between Light And Dark Mode"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="Toggle between single-column and double-column"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside_config" type="button" title="Setting"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="Table Of Contents"><i class="fas fa-list-ul"></i></button><button id="go-up" type="button" title="Back To Top"><span class="scroll-percent"></span><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><script src="https://cdn.jsdelivr.net/npm/@fancyapps/ui/dist/fancybox.umd.min.js"></script><div class="js-pjax"></div><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script></div></body></html>